SQL statement with <> and Null values in the table

[rdemyan via AccessMonster.com]

I have the following SQL statement in code:

strSQL = "SELECT * FROM TABLEA WHERE F2 = 'SITE'

This returns the record that I expect.

However: the following does not

strSQL = "SELECT * FROM TABLEA WHERE F2 <'SITE'


But if I rewrite it as:

strSQL = "SELECT * FROM TABLEA WHERE Nz(F2,'') <'SITE'

that seems to retrieve records where there may be nulls in column F2.

Let me just say that this table is a holding table for an Excel import so
values could be anything.

Is this correct. That is, I have to use the Nz function when using <>. I
haven't been doing this in some other SQL queries and now realize that I may
be missing some rows that should be returned.

Can someone explain this to me?

Thanks.

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200610/1

[rquintal@sympatico.ca]

Re: SQL statement with &lt;&gt; and Null values in the table


rdemyan via AccessMonster.c om wrote:

I have the following SQL statement in code:
>
strSQL = "SELECT * FROM TABLEA WHERE F2 = 'SITE'
>
This returns the record that I expect.
>
However: the following does not
>
strSQL = "SELECT * FROM TABLEA WHERE F2 <'SITE'
>
>
But if I rewrite it as:
>
strSQL = "SELECT * FROM TABLEA WHERE Nz(F2,'') <'SITE'
>
that seems to retrieve records where there may be nulls in column F2.
>
Let me just say that this table is a holding table for an Excel import so
values could be anything.
>
Is this correct. That is, I have to use the Nz function when using <>. I
haven't been doing this in some other SQL queries and now realize that I may
be missing some rows that should be returned.
>
Can someone explain this to me?
>
Thanks.
>

null is not comparable to anything: it is the void.

null = null equates to false
null <null will also be false
something = null will return false
something <null will likewise be false

Something + null equates to null; the only operator that breaks the
rule is the ampersand concatenation operator &

the only way to work with a null is to test for nullness, and match on
the resultant, or convert the null to some other value..
nz() is a shorthand for iif(isnull(vari able),substitut e,variable)

So yes, if you have nulls, but want to equate them with something else,
you have to convert them, as in the examples below

strSQL = "SELECT * FROM TABLEA WHERE F2 <'SITE" or F2 is NULL"'
strSQL = "SELECT * FROM TABLEA WHERE F2 & "" <'SITE'"
as well as your
strSQL = "SELECT * FROM TABLEA WHERE Nz(F2,'') <'SITE'"