Update Query - Criteria Blank Cells??

olimits7

Board Regular
Joined
Oct 29, 2004
Messages
202
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
Try using IS NULL as your criteria.
 

olimits7

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 30, 2003
Messages
1,352

ADVERTISEMENT

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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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,
 

Forum statistics

Threads
1,141,721
Messages
5,708,092
Members
421,546
Latest member
delatollas

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
Top