Update query

[lipin]

I have a table"AttendanceRecords" with fields:
Name - txt
IDNumber - num
Hours - num
AbsenceCode - txt

I have a second table "AbsenceCodes" with 2 fields with codes in them
shoptrac - txt
attendance - txt

I need to lookup the "absencecode" field in table "AttendanceRecords" in the systemcode field of "AbsenceCodes" and replace it with the corresponding code in the "attendance" field.

Tried this update query, it runs, no errors, but nothing changes in the "AttendanceRecords" table.

UPDATE AttendanceRecords SET absencecode = dlookup("attendance","AbsenceCodes", "absencecode = [AbsenceCodes]![shoptrac]");

What am I doing wrong?

 

[Sergeant]

Your criteria is wrong in the DLookup. It is looking for records where the absence code equals "[AbsenceCodes]![shoptrac]" (literally).
Try this:

UPDATE AttendanceRecords SET AbsenceCode = DLookUp("Attendance","AbsenceCodes","shoptrac = '" & [AttendanceRecords].[AbsenceCode] & "'")

 

[lipin]

Thanks Sarge. Flawless!!

 

[Sergeant]

Pat, this was my first thought as well. Looking back at the post, I decided that this person was asking how to get over this hurdle, not how to win the race...so I helped with the hurdle. I should have included a caveat such as the one you added. Thank you for doing so.