Re: DLookup with multiple search criteria

[Ken Snell]

If DLookup doesn't find a value, it returns Null, so you may need to trap
that before you display your MsgBox. Otherwise, the MsgBox will always
display the text, with or without a value from the DLookup function.

But if that's not the problem that you're seeing, you may need to delimit
(with #) the value from the form for the date value:

result = DLookup("VendorName", "Vendor", "Vendor =" & "'" &
Forms!ExpenseHdrFrm!Vendor & "'" & " AND " & _
"Date =#" & Forms!ExpenseHdrFrm!Date & "#")

 

[John Pritchard]

Hi,

Thanks for the reply.

I tried delimiting the date with #'s but it made no
difference.

I was only using msgbox to see what DLookup was
returning ...

What I'm after is the equivalent of :-

select count(*)
from ExpenseHdr
where vandor = <value on my form>
and date = <value on my form>

The e.g. code was my attempt to get Dlookup to do this.

I can get the equivalent of :-

select count(*)
from ExpenseHdr
where vandor = <value on my form>

but I'm doing something wrong with the dlookup criteria
when trying to do the 'and Date ...' part in that the
Dlookup seems to ignore this part of my search criteria
completely.

Hope this clarifies my query!

Cheers

JP