Empty string

  • Thread starter Thread starter levpet
  • Start date Start date
L

levpet

Hi guys,

I was building up a simple equation.
Status: IIf([Staff Name.Location]="","Gone","In house")

for some reason, the "empty string" does not work, and when I run th
query, it shows "in house" for the empty string records as well.
The field type for the Location is text getting data from a query, it
not required and allows zero lengths, please help!!!!

Thx
Le
 
The data is probably Null. However, since you have allowed zero-length
string (ZLS), you must test for that also.

Try:
Status: IIf(IsNull([Location]) Or [Location] = "", "Gone", "In house")

You are better not allowing ZLS because:
- it's a pain and potential source of error if you have to test for both;
- the difference is not visible to the user;
- Access itself can't tell you the difference correctly, i.e. DLookup()
wrongly returns Null for a ZLS.
 
levpet said:
Hi guys,

I was building up a simple equation.
Status: IIf([Staff Name.Location]="","Gone","In house")

for some reason, the "empty string" does not work, and when I run the
query, it shows "in house" for the empty string records as well.
The field type for the Location is text getting data from a query, its
not required and allows zero lengths, please help!!!!
Click to expand...

Shouldn't that be

[Staff Name].[Location]

?

I understand that the Location field allows zero-length strings, but
have you determined whether it actually *contains* them? Have you
verified that the fields that show up as blank are not in fact Null? If
you want to ensure that your expression works regardless of whether the
field is Null or a ZLS, you could try this:

Status: IIf(Len([Staff Name].[Location] & "")=0,"Gone","In house")

or this:

Status: IIf(Nz([Staff Name].[Location], "")="","Gone","In house")
 
Hi Lev,

This suggests the fields that you believe contain empty strings are in
fact Null (which is the more common situation in Access: "not Required"
means "allow Null").

Try
Nz([StaffName].[Location],"")=""
or
IsNull([StaffName].[Location])


Hi guys,

I was building up a simple equation.
Status: IIf([Staff Name.Location]="","Gone","In house")

for some reason, the "empty string" does not work, and when I run the
query, it shows "in house" for the empty string records as well.
The field type for the Location is text getting data from a query, its
not required and allows zero lengths, please help!!!!

Thx
Lev
Click to expand...
 
Back
Top