Testing for null

  • Thread starter Thread starter DZ
  • Start date Start date
D

DZ

My query was getting unpredictable results.
I think I found the source of the problem.
There is field that contains some empty values in some records.

When I test the field for nulls by creating a calculated field in a query
with the expression
IsItNull: IsNull([Field1])

For the records where the field is empty, some records return 0 (Not Null)
and some return -1 (Null). Allow Zero Length is set to "No"

I thought that is a field is empty and the Allow Zero Length property is
set to "No" then the field must be null, but that's not the case here.

Does anyone know why these fields that are empty are returning not null?

Thanks for any help with this.
 
If you set the field's Allow Zero Length property to No *after* you already
have data in the table, the existing zero-length strings don't get converted
to Null.

To test if it is a zero-length string, the expression in the Field row would
be:
IsItZLS: ([Field1] = "")

If it is neither a null nor a zero-length string, it might contains spaces
or other non-printing characters. You can see what the ASCII value of first
character is with:
AscFirstChar: IIf([Field1] Is Null, Null, Asc([Field1]))
and the number of characters with:
Len([Field1])

Hope that helps you pin it down.
 
You are absolutely correct.

I changed the Allow Zero Length property to No, thinking I could get rid of
Zero length data.

Now I'll use the following to test for empty fields in records.
CheckIfEmpty: IsNull([Name]) Or [Name]=""

Thank you
regards
 
By the way, do you an easy way to strip a field of any Zero Length data so
that all fields containing Zero Length Data are Null?

Thsnks
 
Use an Update query. (Update is on Query menu, in query design view.)

In the Update row in query design, enter:
Null
In the Criteria row, enter:
""

Run the query.

Programmatically:
Currentdb.Execute "UPDATE Table1 SET Field1 = Null WHERE Field2 =
"""";", dbfailonerror
 
Back
Top