in access97, this works
SELECT businessId
FROM tblBusiness
WHERE baanPostalId = null


but not in access2007, it needs to be
SELECT businessId
FROM tblBusiness
WHERE baanPostalId is null

is there a compatiblity option that allows the original version to
run ?

Roger wrote:
Given that "baanPostalId = null: should not have "worked" in A97, I doubt
you will get a workaround. It likely ran without error, but it almost
certainly did not return the results you wanted. If it did, it is likely
that baanPostalId did not contain what you thought it did.

Nothing ever "equals" Null, not even in A95, let alone A97. That's part of
the definition of Null: "unknown value" never equals "unknown value". Only a
known value can equal a known value. Unknown can _be_ unknown, but it can
never equal it.

Are you telling me that attempting to use "baanPostalId = null" throws an
error in A2007? If so, all I can say is "Bravo!".

To add to what Bob said, the idea that Unknown = Unknown is not valid. It's
error #5 in this list:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Bob Barrows" <reb01501 (AT) yahoo (DOT) com> wrote

In Access 2003 entering <> Null as criteria, gets automatically changed to is
not Null and = null gets changed to is Null. So that could be the reason that
the = null criteria worked.

Running a quick check with the following
Public Function xxx()
Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = "SELECT Count(*) FROM FAQ WHERE fSubject <> null"
Set rst = CurrentDb().OpenRecordset(strSQL)
Debug.Print rst.Fields(0)
End Function

I get 676 with <> Null
I get 0 with = Null

Which is exactly what I get if I use is Not Null and Is Null.

Personally I would rather the "magic" did not happen. And I guess that in the
change from 2003 to 2007 this "magic" was removed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Allen Browne wrote:

That's not my experience John. I have a table called Cities with a field
called ocity that allows nulls. I added two records without entering
anything for ocity, so the table contained 8 records with non-null values
and 2 with nulls.

Running this query from sql view:
SELECT Count(*) AS Expr1 FROM cities WHERE cities.ocity = Null

resulted in 0, the expected result, since this comparison returns Null (not
True) for all the records. Of course, when I inadvertantly switched to
Design View, Access did its magic and chaged it to "Is Null", and of course,
when I ran it, I got the expected result of 2. I get the same result using
your test function to test "= Null" vs. "Is Null"

Strangely enough, running this:
SELECT Count(*) AS Expr1 FROM cities WHERE cities.ocity <> Null
returned 8, the same result as using "is not null". This I did not expect
(since any comparison with Null is supposed to result in Null, I would have
expected the result of "<> Null" to be 0, since this comparison would not
return True for any of my records), so the magic you are talking about only
occurs with "<>", and I had never noticed it occurring.

I'm off to go test this in SQL Server ...

John Spencer wrote: