Problem with DLookup in Update Query

[matthewnsarah07]

I am attempting to update the field [Team] in tbldata using the [Team] field in tblteam.

I have added the following expression into the Update To section in the query builder

DLookUp("[Team]","tblteam","[Date]=[Date] And [Shift]=[Shift]")

Basically tblteam has a [Date], [Shift] and [Team] and tbldata has a list of events with a Date '01/10/2009' and Shift "Early Shift" for example. All I want to do is match a team using these.

The query works but always returns the the value '3', what is going wrong?

Thanks

 

[vbaInet]

Maybe you want to use the IIF() function. Have a look at that.

 

[matthewnsarah07]

Not sure if the IIF would work as Team 1,2 or 3 can be returned - the DLookup should work

I'm assuming I've missed somthing in the formatting of the expression or something

 

[Galaxiom]

I expect that 3 is the first Team ID encountered in your table. Access could be reading the criteria as always true.

However putting a DLookUp into a query as you have done will result in an incredibly slow result. This task should be done as an update query with a join between the tables on [Date] and [Shift].

Date is a bad name for a field since it is a reserved word.

You should have normalised the shift names to IDs in a table of Shift names.

I suspect other parts of your structure is also not normalised as you seem to be updating a field in a table directly from another table.