Zero Length String?

[M]

What the heck?!? I have a field that is specified as text.
I want to eliminate zero-length strings in this field. I
have tried "", IsNull, = Null, <> Not Null, everything.
What do I use to force the recognition of a zero length
string? I am using DAO to edit a recordset and the table
isn't cooperating.

Thanks

M

 

[M]

Could the fact that a combo box lookup on the table design
be causing the problems with editing the records?

M

 

[Elwin]

A text field can contain both null and zero length values,
but the SQL syntax to filter out each is different

Is Not Null

<> ""

good luck.

 

[Tim Ferguson]

I want to eliminate zero-length strings in this field.

The most explicit way is also the quickest:

WHERE LEN(MyTextField)>0

You seem to be having some confusion with the following, which of course
produces a different set of records:

WHERE MyTextField IS NOT NULL

Comparison with an empty string (WHERE MyTextField <> "") is bad because
Jet has to reserve some memory, create an empty string in it, perform a
byte-by-byte comparison (three bytes, two for the size and one for the end
marker), remember the result, release the memory and then return. The LEN
method simply retreives the size word and compares it with zero: two
machine instructions.

B Wishes


Tim F

 

[M]

You are right --- It is confusing. I hadn't thought of the
Len() approach though. Unfortunately, the empty string is
a necessary criteria in this particular task. I am still
baffled by how my Select Case statement is handling it. In
the past it has been fairly dependable for these editing
tasks. But for some reason this table doesn't want to
cooperate. Anyway, thanks for the Len() advice. It'll come
in handy.

M

 

[M]

Well, I thought the Len() would solve the problem. I added
this:

Len(.Fields("Field1").Value)

and verified through Debug.Print that the value was Null.
BUT --- the code is still not working. I have tried
everything to get Access to recognize this null value but
it still refuses to. This is still a text field and
every "", IsNull, and the Len attempt has resulted in the
same outcome. It knows the value, why isn't it working?!?!?

Thanks to everyone for the help.

M

 

[M]

OKAY --- After trying all of the solutions that everyone
generously offered, (and all worked, just not in this
particular situation) I found that the NZ function finally
solved the problem.

I used it like this:

Select Case Nz((.Fields("Field1").Value),0)

Case 0
.Fields("Field2").Value = "RESULTS"

End Select

Thanks to everyone who helped with this.

M

 

[Tim Ferguson]

Len(.Fields("Field1").Value)

and verified through Debug.Print that the value was Null.

I am confused.

If the field is NULL, then

? n(.Fields("Field1").Value)
Null


If the field is "", then

? n(.Fields("Field1").Value)
0

So what _are_ you testing for? Nz will happily force a Null into a ZLS,
which you can test for by method B but it's a long way round.

Another thing crosses my mind after your other post below. I have been
assuming this is happening in SQL. If you are looking for a VBA solution,
the IS NOT NULL will obviously not work but Not IsNull(MyField.Value) will
do exactly the same thing. Then again, you should be filtering records in
SQL not in VBA.

B Wishes


Tim F