Update Query - Criteria Blank Cells??

[olimits7]

Hello,

I'm trying to do an Update Query for blank cells for one of the fields in my database, but it doesn't update any of the blank cells.

What am I doing wrong??

Under Criteria I put [fieldname]="" but nothing happens. It says you are about to update 0 rows.

Also, the field is a Text field.

Thank you,

olimits7

 

[Joe4]

Try using IS NULL as your criteria.

 

[olimits7]

Yes, that worked. Thank you.

Just curious why doesn't the other way work [FieldName]=""?? I thought it would be the same thing.

Thank you,

olimits7

 

[Joe4]

I think it is one of those bizarre Access things, where it treats the Null value differently than the empty string.

There is probably a more "technical" explanation somewhere, that you may be able to find if you search the net.

 

[mdmilner]

It depends on the type of data in the field.

Sometimes, depending on the field type, you can test for the length of the field contents in your parameter. len([fieldname])=0

Stick with what works.

Mike

 

[VicRauch]

I don't know where I saw this, but it was within the last few weeks, and I've been working with Access for over 10 years. It is a great, and accurate way to explain the Null thing. Null mean "Unknown." A 0 in a numeric field is something, and a zero length string is something. They both are "Nothing" but we know they are nothing. But a Null is unknown, therefore we can not know what it is. Therefore we can not match a "known" of "" or 0 with an unknown. We can ask if something is unknown, then .... That is why we have to use Is Null (or better, is unknown) when working with nulls.
HTH,

 

[Andrew Fergus]

These links may help :
http://office.microsoft.com/en-us/assistance/HP030719791033.aspx
http://office.microsoft.com/en-us/assistance/HP051885341033.aspx
Andrew