DLookup and Numerical Values

[The Professor]

Hi,



Having a problem finding the right syntax for using numerical variables with DLookup. The code I have written, which is throwing up an error 'lngNo', is this:

lngID = DLookup("CustID", "tblInvoice", "[BookingID] = lngNo")

lngNo is a long varible, as each time the code is run, the value is likely to be different. If I hard code it thus:

lngID = DLookup("CustID", "tblInvoice", "[BookingID] = 7")

then it works fine, but is useless to me. I'm missing something simple here, I know, but at present it's eluding me.

[JoeM]

Is lngNo a variable?

If so, then maybe try:
lngID = DLookup("CustID", "tblInvoice", "[BookingID] = " & lngNo)

[The Professor]

Yes, thank you, that did the trick.
That pesky ampersand - now why didn't I think of that!


Regards, Prof.

[JoeM]

Yes, if you include the variable name inside the quotes, it is treated as a literal text value, and not a variable.
So you were literally passing the argument the text "lngNo", instead of the value of the variable lngNo.

[The Professor]

I did try it outside the quotes, as thus:

lngID = DLookup("CustID", "tblInvoice", "[BookingID]" = lngNo)

But that just produced a type mismatch error - I couldn't seem to win!

[JoeM]

Very close. The equals sign needs to be within the quotes too.