Null value in query

lman

New member
Local time
Today, 03:04
Joined
Feb 22, 2011
Messages
9
I'm sure the answer to this is simple, but i can't see the wood for the trees.

I have several fields in an access query that i am passing parameters values to through VBA. I use the following statement in the criteria field of the query to check if the value passed is null

Code:
IIf([oper] Is Null,"*",[oper])
[oper] is the parameter, and if null it displays all the records ("*").

This works fine expect for a field that has a date/time data type. (data type mismatch). I have tried something like

Code:
IIf([oper] Is Null, > 01/01/00 ,[oper])
this doesn't give an error but doesn't display anything.
 
You could try setting the param in VBA.

Just before the param is sent do a quick
Code:
If isnull(oper) then
    oper = "> #01/01/2000#"
End if

Or something similar.

That way a null value will never be sent to the query so it will never have to deal with it.
 
Thanks i'll give it a try...i did try something similar but i forgot to put the #'s in..i'll let you know how i make out...
 
Well that didn't work but i did find a solution, i noticed if you just put an * in the criteria access modifies it to Like "*" so i changed it to

Code:
like [oper] & "*"

and it works perfectly...

thanks for your help!
 

Users who are viewing this thread

Back
Top Bottom