Update query (1 Viewer)

[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!!

 

[Pat Hartman]

Sorry but DLookup(), although it may work, is the worst possible way to do this. All you need to do is to join the two tables. You would draw the join lines between absencecode and shoptrac. The join is much more efficient than running a complete query, which is what the Dlookup() is doing, for each row of the other table.

You may not see any real speed difference if you have only a small table but once you get more than a few hundred rows, the speed difference will be striking.

 

[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.