Incorrect syntax using Dlookup with a nested DMax

[craig1988]

SSer = Nz(DLookup("EndSerialNo", "qryG2OrderHistory", "AccNo=" & DMax("AccNo", "qryG2OrderHistory")), "N/A")

Hi All



I believe I have invalid syntax here.

What I am trying to achieve is to look up the End Serial value in the query "qryG2OrderHistory" where the AccNo is equeal to the one appearing on a form. I want to return the newest EndSerialNo by using the DMax.

Any suggestions??

[lfpm062010]

Is "AccNo" a "Text" or "Number" data type?

If it is "Text" then you need quotes (single or double).
SSer = Nz(DLookup("EndSerialNo", "qryG2OrderHistory", "AccNo='" & DMax("AccNo", "qryG2OrderHistory")) & "'", "N/A")

[John_G]

...where the AccNo is equeal to the one appearing on a form.

Your statement doesn't contain any reference to a form field, so it won't work as written, and the DMax should be where the DLookup is. Try something like this:

SSer = Nz(DMax("EndSerialNo", "qryG2OrderHistory", "AccNo=" & [formcontrol]), "N/A")

where [formcontrol] is the form control with the account number.