Help deleting records "look" empty records
Dear Bud:
>> I would be very grateful for any hell or guidance.
I will give you some guidance and let somebody else give you hell :)
The isnull function will check for nulls. You can use the len function to check
for zero length strings.
I'm not sure, but what you might need is a
if (len(nz(rs![fldname], "")) = 0) then
<code>
end if
The nz function will change a null into a zero length string. It will return
the value of the field if the field is not null. That way the len function
won't give an error on nulls.
However, if you can use a query or SQL instead of code it will be much faster.
Using code to step through a recordset one record ata time is pretty slow,
although sometimes necessary.
Sincerely,
Dave Burt
Quote:
> Hi,
> Can someone please help me out with this. I can't get the syntax right or am
> not doing things the right way. I am importing all these excel sheets. I
> have created some temporary qry defs that work okay(most of the time).
> All these records have as many as 17 to 21 fields in them. When importing
> and I call my delnull sub, it takes a pretty good chunk of them away. Yet
> many are left.
> I have gotten lucky a couple of times using the regular query, copy the sql
> statement. There are either spaces or something else in a (or multiple
> fields that throw wrench into things.
> here's some of the code
> I know it will take up space, but at this point, I just want to get rid of
> these records.
> *****start********
> Set rs = dbs.OpenRecordset(tblname, dbOpenTable)
> rs.MoveLast
> rs.MoveFirst
> With rs
> Do While Not .EOF
> If (rs![F1]) = "aa" Then
> .Delete
> End If
> .MoveNext
> Loop
> Close
> End With
> Set rs = Nothing
> ***end********
> This works for the dummy rows . I just can't figure out ho to use a len
> combined with a trim or how to look for a null string.
> There must be something in one or more of the fields. Because even building
> a query, putting the fields and setting the criteria to is null, doesn't get
> it. The annoying thing is that it doesn't always occur in the same field.
> I would be very grateful for any hell or guidance. It must karma or
> something, the little assistant know what I mean when I type "Len" in the
> box(yes, I have the search for programming box checked....)
> Thanks in advance,
> Bud Dean
