Using Dlookup in Update Query criteria
Posted on 2002-04-15
In Access 97, I have a source table that list 10 different factors for each month of the year.
After the application's user identifies the month they want to process, I want to populate another working table's fields with just that month's factors. The application will then use this table's fields as a source for performing calculations and creating headings in various reports.
I can accomplish my task by creating a working table recordset and then using a Dlookup function to assign a value from the source table into each field.
rstWorkingTable![NM].Value = DLookup("[NM]", "SourceTable", "[AB] = '" & strMonthID & "'")
rstWorkingTable![Name].Value = DLookup("[NAME]", "SourceTable", "[AB] = '" & strMonthID & "'")
rstWorkingTable![OVER30].Value = DLookup("[OVER30]", "SourceTable", "[AB] = '" & strMonthID & "'")
Instead of changing the value of each field individually, is it posible to use an Update query to change all the fields at once in the working table's record? If so, how is the SQL written to accommodate all the fields?