DLookup with Null Values

[sanders720]

I have a dLookup Statement that is not working, because it cannot evaluate null. I do not want to use Nz here, because what if my query needs a null to work?

=DLookUp("[Hours]","timecards","[Employee Number]=[txtEN] AND [Date]=[txtPS] AND [Job Number]=[txtJobNumber] AND [Order Number]=[txtOrderNumber] AND [Op Number]=Nz([txtOperationNumber],"&quot&quot

The problem is here...

[Order Number]=[txtOrderNumber]
This value could be null, and I would like to search it as if it were, otherwise if I do something like...

[Op Number]=Nz([txtOperationNumber],"&quot

Then the value is evaluated as a space, and the data is not properly looked up.

Any thoughts, and thanks for the help!

 

[cheerio]

In effect NULL just means DON'T KNOW.

By definition if the database does not know what you are searching for then it cannot return an answer.

If you want to interpret blanks on your form as meaning YOU DON'T CARE then you probably need to test for blanks and run a different query for each case blank case. Each such query will not include a test against the field that is blank.

The only other possibility is to use a like operator with a wildcard appended. For example, if your operation numbers go from 1 to 9 the query would evaluate as LIKE "9*" if the user keys a 9. If the field is blank it will evaluate as LIKE "*" so you have a wildcard. However you have a problem if the operation numbers go from 1 to 11 as LIKE "1*" will select both 1 and 11.

Hope this gives you some ideas.

Ken

 

[jfgambit]

Sanders:
Have you considered an If statement before the Dlookup function:

If IsNull([Me.txtOrderNumber]) then
YOUR DLOOKUP CODE HERE
Else
YOUR DIFFERENT DLOOKUP CODE HERE
End if



"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[sanders720]

Okay, I'll figure something out for this one. Here's my next problem. I'm trying to add the columns of my dLookup statements in my report. Looks something like this...

M T W H F S S
dL1 dL1 dL1 dL1 dL1 dL1 dL1
dL2 dL2 dL2 dL2 dL2 dL2 dL2
---------------------------------
T T T T T T T

I've tried SUM, such as setting the comtrol source to...

=SUM(txtMonNumber)

OR

=SUM(val(txtMonNumber))

No help there. Any other ideas, and thanks!