Access not recognizing a field as being empty.

Question

I have a database that has a ShipID field that may or may not be filled with a number.  Access is not recognizing the field as being empty when there is no data there.  When I do a query with the criteria of "null" it does not find any of my records.  It appears that Access thinks there is data in the field when there is not.  Why is it not finding my records?

Answer 1

Check the field in its table design.  If its a Text or Memo field, make sure its Required and AllowZeroLength properties are both set to No.  This will make sure new records will have Null if they don't have anything in the field.

Then. after making a backup, run an Update query to fix the existing records:

   UPDATE thetable SET thefield = Null WHERE thefield = ""

If you can not correct the table's design, then you will have to modify everywhere you check for null to also check for a zero Length String.  In a query it could be by using the criteria:

   Is Null OR =""

In a control source expression or in VBA codeL

   Len(Nz(Me.thefield, "")) = 0