I'm writing a query, and I get odd results. I have a table with several fields. One of these fields is empty in some of the records (about 3500 records). I'm trying to use a Select statement to get the records with the blank field.

I have tried two different methods which <should> give the same number of selected records, but they don't.

In quasi-SQL:
First Method:

Where Field=""

Second Method:

Where Field Is Null

The ="" method yeilds 24 records. The Is Null method yeilds (the correct) 79 records.

Why do these Select modifiers behave differently? Is it an Access bug/oddity?

Baffled,
-Zeke

Peter,
Thanks for the quick answer. I think I get your meaning. I had been using Access' 'filter by' feature to determine the 'correct' number of rows.

Checking for overlap, as you mention - shows that the results from ="" and Is Null are indeed independent data sets (I should have seen that - I still wouldn't have known why though). I guess not all blank fields are created equal!

Thank you very much. The data has now been properly cleaned using:

UPDATE TABLE.FIELD = Null
WHERE (((TABLE.FIELD)=""));

-Zeke

Which is correct. You really don't want to be storing empty strings ("") in your tables.

How would an empty string get into the table to begin with? Could it get there by deleting pre-existing text from the field?

-Zeke

You can certainly INSERT or UPDATE a field with an empty string. Someone put it in there. It may have been something like an automated process that imported data from an external source, and imported empty strings as empty strings rather than checking them to see if they were empty and converting them to NULL.

I don't think that John's assertion that empty strings shouldn't exist is entirely accurate. NULL means that the value does not exist. An empty string means that it does exist, but is blank. I'm trying to come up with a good reason for a difference, and am coming up short. But here's a somewhat questionable example:

Say you're entering people's names into a database, first, middle, and last. You might not have some people's middle names available when you enter the data, so you'd leave them as NULL, meaning that the data does not exist. Later, when completing the data entry, you put in people's middle names, replacing the NULL. But then you come across someone who doesn't have a middle name at all. You might then want to change that field to the empty string, indicating that the value does exist, but it is empty. Of course, all of that is a logical construct. You could conceivably design it to be the other way around. It's all about how you interpret your data. (Though the way I describe makes more sense to me.)

So it's possible that the data you found that had empty strings may have had a different meaning than the data that had NULL values. Or it may have been a data entry error. Only a definition of what the database fields mean for your situtaion can really let us know.