Field entry not recognized as blank (='' vs IS NULL)

[fboehlandt]

Hi everyone,
I have the following problem: I have two access databases that contain the same tables and fields within (it is an entirely different matter why the two have not been combined yet...). One of the tables is titled 'Information' and the field in question is 'UserDescription' (set as text field). 'UserDescription' contains one of 10 values or, alternatively, may be left blank. Her is the strange part when I run a query extracting blank entries for both databases:
In the first I would have to run something like this:

WHERE Information.UserDescription = ''

for the second database, however:

WHERE Information.UserDescription IS NULL

Apparently, the blank entries in the UserDescription fields of the two 'Information' tables are different from one another. Why? Seems to me like they contain zero-length strings only! As to why this is bothering me: I intend to use a single query to extract data from both databases and export the results to an Excel pivottable (which works just fine for all entries that are <>"" or IS NOT NULL). For some reason, in the command text field of the connection properties (for the Access uplink from Excel) I cannot use IS NULL, but ='' may be used. Thus, the following will not work either:

WHERE Information.UserDescription = '' OR Information.UserDescription IS NULL

Can anyone help please? I'm out of ideas. Cheers

 

[fboehlandt]

Sorry, jumped the gun there...The query below DOES work (Typo )

WHERE Information.UserDescription = '' OR Information.UserDescription IS NULL

Alas, I'm still not sure why I have to use it, though...

 

[DCrake]

Try

Nz(Information.UserDescription,"") = ""

This handles both zero length strings and Null values

 

[fboehlandt]

@DCrake
thanks for you quick reply. Nz() is an Access function and works fine for the queries in Access. Any ideas how to implment it in SQL? thanks in advance

 

[Galaxiom]

There is a difference between the Null String and Null.

Null is Nothing, Nulla, Nada, Nichts, Nechevo, Rien.

The Null String is a value of the Zero Length String. It is a string with no characters.

They are different which is why they must be tested separately or combined and tested as shown by DCrake.

 

[DCrake]

In exactly the same way. Does your reference to SQL implies the back end is SQL Server or is the back end an Access database?

 

[fboehlandt]

@Galaxiom:
that is exactly what I suspected but I was very surprised that not both of them are null string. I have no information on how the access databases were compiled so that is to remain a mystery.

@DCrake:
sorry for the confusion, the back end is an Access database. But still no joy using Nz(). Tried to implement it in the command array as follows:

Nz([Information.UserDescription], '') = ''

which does not work. The following works fine, however:

(WHERE Information.UserDescription = '' OR Information.UserDescription IS NULL)

I recon my syntax may be incorrect. Please let me know what I'm doing wrong.

 

[DCrake]

Spotted the deliberate mistake

Nz([Information.UserDescription], '') = ''

Should read

Nz([Information].[UserDescription], '') = ''

 

[Galaxiom]

@Galaxiom:
that is exactly what I suspected but I was very surprised that not both of them are null string.

If a data entry operator backspaces the characters out of a field it leaves a ZLS.