How to use dlookup in access 2007 with multiple criteria, one of them being in date format?

Question

I have a Advance/Miscellaneous Exp table where there are 4 fields as follows, Employer Name, Advance Expense, Miscellaneous Expense, Adv_Miscl_Date.

I have a form something like this Employee Name: Last_date: Advance: Miscellaneous:

When the last_date field gets focus, it displays the latest date from advance/miscellaneous exp table with respect to the employee name. Now in the next field i.e the Advance field, I want the latest advance to be displayed. I tried the following with dlookup but kept getting syntax error or mismatch error.

Private Sub Adv()
[Adv]=Dlookup("Advance","Adv_Miscl_Exp","[Emp_Name]=Forms![Calculate Salary]![Emp_Name]" AND "    [Adv_M_Date]=Forms![Calculate Salary]![Last_date]") 
End Sub

What is the right syntax? How do i use dlookup with one criteria as employee name and the other criteria as the advance/miscellaneous date

Any help is highly appreciated. Thank You

Answer 1

It would be something like this:

Private Sub Adv()
    [Adv] = Dlookup("Advance", "Adv_Miscl_Exp", "[Emp_Name] = '" & Forms![Calculate Salary].Emp_Name & "' AND [Adv_M_Date] = #" & Forms![Calculate Salary].Last_date & "#") 
End Sub

For the correct syntax on DLookups, I tend to use the following page; http://access.mvps.org/access/general/gen0018.htm