Error using Dlookup function in Access2007

I am using the following Dlookup function in a report in Access2007; The function always returns the first record of t开发者_如何学运维he query even though I change the criteria. Here is the code:

    =DLookUp("Result","Query1","[Date_Registered] = # " & "2011/02/01#" And "[Department] =" & 'IT')

Based on the criteria above, the value returned should be in record#4, but always return the value of the first record of the query. Help please. Thanks,

The criteria you're giving DLookup is not a valid VBA string. If you break that out, and feed it to Debug.Print like this, it will trigger a compile error:

Debug.Print "[Date_Registered] = # " & "2011/02/01#" And "[Department] =" & 'IT'

I think you should build your string and load it into a variable, then Debug.Print the variable, and finally hand the variable to DLookup as the third (criteria) parameter. I don't know where the date string and 'IT' are coming from, but this should point you in the right direction:

Dim strCriteria As String
strCriteria = "[Date_Registered] = #" & "2011/02/01" & _
    "# AND [Department] =" & "'IT'"
Debug.Print strCriteria
Debug.Print DLookup("Result", "Query1", strCriteria)

The first Debug.Print statement in that code fragment will print this to the Immediate Window:

[Date_Registered] = #2011/02/01# AND [Department] ='IT'