Dlookup Issue. Finding Data Using Multiple Criteria and Updating Table

[MintChipMadness]

I did some searches but can't find my exact problem. I will try to explain what I am doing so everyone can visualize it. I am trying to mass add some information from an import and to do so I need to do a lookup of one table to fill in the value on another. I listed the 2 tables below with their corresponding fields. I did an update query on ImportTest table with only Category_ID from the ImportTest table showing. I want to update it to the [Directory].[ID] value where the Department, Division, Function and Category are equal for both tables. There is only going to be one record that matches. I listed the expression I have been working on below in code tags.


Directory (Table)
ID <--Number I need
Department (Text)
Division (Text)


Function (Text)
Category (Text)

ImportTest (Table)
ID
Category_ID (Number)<--need to fill in number here
Department (Text)
Division (Text)
Function (Text)
Category (Text)

=Dlookup ("ID","Directory","Department = [ImportTest].[Department] And 
Division = [ImportTest].[Division] And 
Function = [ImportTest].[Function] And 
Category = [ImportTest].[Category]")

[June7]

What was the outcome of your test?

Variable criteria need to be concatenated. References to the ImportTest fields are variables.

Therefore, concatenate the fields (assume all fields are text type):

=Dlookup("ID","Directory","Department='" & [ImportTest].[Department] & "' And Division='" & [ImportTest].[Division] & "' And
Function='" & [ImportTest].[Function] & "' And Category='" & [ImportTest].[Category] & "'")

[MintChipMadness]

Thank you for your replay. I was just going to post that I figured it out when I saw you answered. The solution I figured out is below. You have single quotes on the outside when I have them on the inside but I am guessing it doesn't matter if we're consistent with whatever method we use.

DLookUp("[ID]","Directory","[Department]='" & [ImportTest].[Department] & "'And [Division]='" & [ImportTest].[Division] & "' And [Function] ='" & [ImportTest].[Function] & "' And [Category] ='" & [ImportTest].[Category] & "'")

[June7]

What you posted is the same as my suggestion, except for a missing space in front of the And before [Division].

Order does matter. Single and double quotes must be in pairs and single quotes must be within double quotes.