Dlookup in Update Query

[RiderJohn]

Hi Experts,

I am trying to use a Dlookup in Update Query. There are 2 critierias but it gives an "unknown" error in the second criteria. What is not right here. Please help:

DLookUp("[tbl_temp]![UnitCount]","[tbl_temp]","[tbl_temp]![State]='NY'" & " AND [tbl_temp]![Name] ='" & [tbl_Emp]![Name]'")

Basically tbl_temp is
EmpName State Count
AAA NY 4
AAA CA 2

tbl_Emp has more details:
EmpName State Count City Remarks StartDate
AAA NY - Manhattan old 1/1/2007
AAA CA - LA new 5/1/2007

What I want to do is UPDATE tbl_Emp with data from tbl_temp where ...... EmpName & State match like so:

EmpName State Count City Remarks StartDate
AAA NY 4 Manhattan old 1/1/2007
AAA CA 2 LA new 5/1/2007

Thanks,
RJ

 

[RiderJohn]

actually let me clarify .... the STATE is a datafield ....sorry for the confusion

the final data in tbl_Emp will look like:
EmpName NY CA StartDate
AAA 4 2 1/1/2007

 

[RiderJohn]

anyone? pls. help..... sorry for this msg. just making sure this doesn't get buried.

 

[JerryKlmns]

Does this help?

DLookUp("[Count]", "[tbl_temp]", "[State]='NY' AND [EmpName ]='" & [tbl_Emp].[Name] & "'")

BTW, do not use reserved words for table/query fields, like Count