= null in Access 2000

[thebolly]

Hi all, first time here!

I've just been upgraded at work to access xp from 97 and notice that, in my vb programs where I use strings to fire off sql to access, the WHERE variable = NULL no longer works and I have to change them all to WHERE variable IS NULL

is this correct or have I messed up some??


please confirm to save my sanity

thanks

Ian

 

[jfgambit]

Ian

I use the following in Access 2000 and XP:

If IsNull([Name of field]) then...

or If Not IsNull([Name of field]) then...

HTH

 

[Jacob Mathai]

In a query

'WHERE fieldname is NULL' is correct syntax.

'WHERE fieldname is not NULL' is also correct syntax.

 

[Pat Hartman]

Since "= Null" is non-standard syntax, I'm surprised that it ever worked. But I tested it and in fact the bad syntax worked in A97 but does not work in AXP. Try to stick to standard syntax in the future.

You really shouldn't have too much trouble if all your SQL is in code modules. I don't recommend this since querydefs are much more efficient, but in this case, you lucked out. A global search and replace will fix up your non-standard syntax.

 

[thebolly]

cheers everyone, you've set my mind at ease..

but only = null seems to work in other parts of the query i.e. update table set variable = null where another variable is null !

which of course is also used lots in my program, so a global replace would be messy

Ian

 

[Pat Hartman]

The SET statement is an imperative statement and therefore quite different from a conditional.

Null is a very special value and you need to understand it. As I said, I was surprised that SQL worked, VBA would NOT have. Here's the help entry from the IsNull() function which tells you explicitly that your statement will NOT work in VBA.

IsNull()
Returns a Boolean value that indicates whether an expression contains no valid data (Null).

Syntax

IsNull(expression)

The required expression argument is a Variant containing a numeric expression or string expression.

Remarks

IsNull returns True if expression is Null; otherwise, IsNull returns False. If expression consists of more than one variable, Null in any constituent variable causes True to be returned for the entire expression.

The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string (""), which is sometimes referred to as a null string.

Important Use the IsNull function to determine whether an expression contains a Null value. Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False.