Access 2003 and IsNull

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a mature Access db, originally developed in Access97 that has run fine for years. I just went into it to do some upgrading, using Access 2003 for the first time, and any query using IsNull(xx) won't compile. If I switch to xx Is Null they work. I can't find any documentation regarding not being able to use IsNull anymore. I don't believe it has anything to do with the sandbox mode introduced in 2002, although I did try it after setting Sandbox level to 0 just for kicks. No Dice. My only other theory is that the query makes you use Is Null so it doesn't have to use the VBA library where IsNull is located, therefore speeding up the query execution (maybe!). Anyone have any idea why this is??
 
Chances are you have a problem with references. See:
http://allenbrowne.com/ser-38.html

It is preferable to use Is Null in a query, because JET can handle the
condition without having to pass control to the VBA library to get the
result of the IsNull() function. In some cases (where indexes are involved)
the difference could be signficant, but it should not prevent the query from
working unless there is a references problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

PRL said:
I have a mature Access db, originally developed in Access97 that has run
Click to expand...
fine for years. I just went into it to do some upgrading, using Access 2003
for the first time, and any query using IsNull(xx) won't compile. If I
switch to xx Is Null they work. I can't find any documentation regarding not
being able to use IsNull anymore. I don't believe it has anything to do with
the sandbox mode introduced in 2002, although I did try it after setting
Sandbox level to 0 just for kicks. No Dice. My only other theory is that the
query makes you use Is Null so it doesn't have to use the VBA library where
IsNull is located, therefore speeding up the query execution (maybe!).
Anyone have any idea why this is??
 
Allen
Thanks for the reply. The first thing I did was check the references, all ok. All my vba code (Lots and lots!) compiles fine, so I was pretty sure that wasn't it. Almost all of my queries that use IsNull also use IIF, so I never really worried about the performance hit with IsNull since IIF loads VBA anyway.

I have solved my problem by changing the queries, now I am just curious. Anyone else have any ideas???
 
I think this is a longshot, but since the isnull()
function returns true or false, is it possible that the
criteria = true or = false is required?
-----Original Message-----
Allen,
Thanks for the reply. The first thing I did was check
Click to expand...
the references, all ok. All my vba code (Lots and lots!)
compiles fine, so I was pretty sure that wasn't it.
Almost all of my queries that use IsNull also use IIF, so
I never really worried about the performance hit with
IsNull since IIF loads VBA anyway.
I have solved my problem by changing the queries, now I
Click to expand...
am just curious. Anyone else have any ideas???
 
Ted
I don't believe it is a syntax issue because I experimented with with several different permutations, including simply making it a field (Expr1=IsNull("X")) instead of using it in the criteria. Thanks for thinking about it, though. I wonder if it is something local to my machine, if anyone has Access 2003 I would appreciate it if they could run a quick trial and see if it is accepted on their machine. I only have the one machine with 2003 installed.
 
Back
Top