Can no longer use "" for Null in queries

[Guest]

I'm working on a database that runs update queries to refresh tables. It
updates some fields to "" (which I thought was the same as Null). Now
anything else I use to find null records won't find these but when I look in
the table they are empty. We have many databases that use queries that append
"" or update to "" that now have to be changed to the word "Null." Does
anyone know why this would have changed on our computers? We have not run any
updates.

Thanks,
Emmy

 

[Guest]

Hi Emmy,
"" is a 0 - lenght string and is not Null.
If you needed to you could change all your 0 lenght fields back into null
using the find and replace option.

hth
Joel

 

[SusanV]

Hi Emmy,

As you have found, "" is not the same as Null - '' is actually an empty
string rather than a null value. So in order for your queries to continue to
work as expected, you'll have to provide testing for both values:

SELECT * FROM MyTable WHERE MyTable.MyField Is Null OR MyTable.MyField = ""

 

[Guest]

Hi Emmy

There is a difference between "" which is a zero length text string and a
Null value which is an empty field. Tricky I know but that is how Access
works. If you are storing the value as "" then you need to set the criteria
as "", if the field is empty you can use the Is Null criteria. HTH

Sheila

 

[Van T. Dinh]

Nope. Null is different from an empty String.

"" has a data type String and has value empty. Null is unassigned and has
no data type. Both of these will show blank in Datasheet View of the Table.

The "Required" and "AllowEmptyString" for the Field might have been changed.

To check for both Null and empty String (and blank white-space characters)
for FieldX, use:

....
WHERE Len( Trim( [FieldX] & "" ) ) = 0

 

[John Vinson]

I'm working on a database that runs update queries to refresh tables. It
updates some fields to "" (which I thought was the same as Null).

It's not. NULL means "this value is uninitialized, unknown,
unspecified". "" means "I know exactly what this value is: it is a
string of zero length".

"" has NEVER been treated the same as NULL. Until recently it wasn't
even a valid value in a Text field; I believe in A2000 the "Allow Zero
Length String" property was added to text fields in table design.

Now anything else I use to find null records won't find these but when I look in
the table they are empty. We have many databases that use queries that append
"" or update to "" that now have to be changed to the word "Null." Does
anyone know why this would have changed on our computers? We have not run any
updates.

They're not changed to the *word* NULL; they're set to this mysterious
non-value Null. By default, Access trims trailing blanks, and - again,
by default - does not store zero length strings.

You can open the table in design view and - if you really want to -
set the Allow Zero Length property of this text field to Yes. If you
do so, it may be prudent to also set its Required property to TRUE -
in most circumstances, it's very hard to visually distinguish a zero
length string from a NULL, and they will behave differently in
queries.

To find records containing a NULL value in a field (of any datatype,
not just text) you can use the query criterion

IS NULL

Note that if the field in fact contains "" this will not match, and
correspondingly, a criterion

= ""

will not fine records where the field is NULL.

John W. Vinson[MVP]

 

[Guest]

As the others have said, there is a difference between a null and empty
string. It's not that easy of a concept. I tell my students that a Null means
that you do not know. An empty string means that you do know that it is
nothing.

Example 1: What color is my car? You don't know so that would be a null.
Example 2: What is my middle initial (and you know that I don't have one)?
That would be an empty string or better yet something like NMI to take it's
place.