Another DLookup Question

[Mac]

Dear All,

Like so many I'm getting headaches and mildly suicidal over syntax for
DLookup. I have a dlookup that looks at an integer value and will
hopefully return another integer value from another table as long as it
is within a set range. The formula is shown below, when I run it, I'm
getting an error. Can anyone help?

Thanks in advance.

Andy

=DLookUp("Startnumber","ChequeBooks","forms!ChequeBookRecord!ChqNo=" &
"([Startnumber]<=[ChqNo]=>[EndNumber])")

 

[Allen Browne]

Try:
=DLookUp("Startnumber", "ChequeBooks",
Nz(Forms!ChequeBookRecord!ChqNo,0) & " Between [Startnumber] And
[EndNumber]")

If this text box is actually on the ChequeBookRecord form, just use:
=DLookUp("Startnumber", "ChequeBooks",
Nz(ChqNo,0) & " Between [Startnumber] And [EndNumber]")

This assumes ChqNo is a Number type field. If it is a Text field, you need
extra quotes.

The Nz() avoids the error that would occur when ChqNo is blank (e.g. at a
new record.)

 

[Mac]

Dear Allen,

Yet again, many thanks, it worked a treat.

Regards

Andy