Update Query - Criteria Blank Cells??

olimits7

Board Regular
Joined
Oct 29, 2004
Messages
229
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try using IS NULL as your criteria.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,218,560
Messages
6,143,204
Members
450,469
Latest member
brent3162

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top