Dlookup with multiple criteria error

[asrul]

Hi All,
my dlookup function with multiple criteria won't work

varT=DLookup("[HourlyTarget]", "DailyTarget_tbl", "[IDLine]=" _
& Forms![DailyTarget_frm]!IDLine_txt AND [WorkDate]=
Forms![DailyTarget_frm]!WorkDate_txt )

varT is variant
HourlyTarget is number
IDLine is Text
WorkDate is Date/Time

It sends me
'Type mismatch'.
error

What's wrong?Please help.

 

[fredg]

Hi All,
my dlookup function with multiple criteria won't work

varT=DLookup("[HourlyTarget]", "DailyTarget_tbl", "[IDLine]=" _
& Forms![DailyTarget_frm]!IDLine_txt AND [WorkDate]=
Forms![DailyTarget_frm]!WorkDate_txt )

varT is variant
HourlyTarget is number
IDLine is Text
WorkDate is Date/Time

It sends me
'Type mismatch'.
error

What's wrong?Please help.

1) You need to use the & in the syntax just before the word AND and
also add the necessary quotes (see #2).

2) As IDLine is text datatype, it's value must be enclosed within
quotes. You need to double them up as the text value is itself within
a criteria string.
"[IDLine] = """ & Forms!etc... & """ And .... etc"

When run, the above will result in a criteria something like this...
"[IDLine] = "Jones" AND ...... etc."

3) Dates must be enclosed within the date delimiter "#" and the date
value must also be concatenated into the criteria using "&".

So, to put it all together, try:

varT=DLookup("[HourlyTarget]", "DailyTarget_tbl", "[IDLine]=""" _
& Forms![DailyTarget_frm]!IDLine_txt & """ AND [WorkDate]= #" _
& Forms![DailyTarget_frm]!WorkDate_txt & "#")

When run, the above will look like this:

varT=DLookup("[HourlyTarget]", "DailyTarget_tbl", "[IDLine]= "Jones"
AND [WorkDate]= #5/4/2009#")