Dlookup with Mutiple Criteria

[JayAndy]

Hi All

The below syntax l have created from the information on the web but still get a Syntax error.

DLookup("ID", "Bill", "Customer = " & Forms!Bills!Customer _
& " AND Date = '" & Forms!Customer!Today & "'")

What am l doing wrong.

Thanks

 

[Ranman256]

dont breakup the command using underscore
be sure to use brackets to show fields (as opposed to functions...date)
you dont need the full path of field if you are in the form....if in a query DONT use dlookup.
use # as a delimiter for dates, not quotes.

DLookup("ID", "Bill", "[Customer]=" & me.Customer & " AND [Date] = #" & me.Today & "#")

 

[Minty]

First of all rename your [Date] field to something else - Date is a reserved word and will cause you no end of pain.

In any vba criteria you need to enclose any text based fields in quotes ' ' and dates must be enclosed in the hash delimiter #

So assuming Customer is text and we rename your date field to MyDate then it should be

DLookup("ID", "Bill", "Customer = '" & Forms!Bills!Customer  "' AND MyDate = #" & Forms!Customer!Today & "#")

 

[JayAndy]

First of all rename your [Date] field to something else - Date is a reserved word and will cause you no end of pain.

In any vba criteria you need to enclose any text based fields in quotes ' ' and dates must be enclosed in the hash delimiter #

So assuming Customer is text and we rename your date field to MyDate then it should be

DLookup("ID", "Bill", "Customer = '" & Forms!Bills!Customer  "' AND MyDate = #" & Forms!Customer!Today & "#")

Thanks but when VBa does like "' AND MyDate = #" Part and comes up with error: Complie error: Expected list separator or )

 

[Minty]

Doh - missed a & out try this

DLookup("ID", "Bill", "Customer = '" & Forms!Bills!Customer [COLOR="Red"]&[/COLOR] "' AND MyDate = #" & Forms!Customer!Today & "#")