Fix a Microsoft Access Update Query for a Lookup Field
Question details
Update an Access lookup field for a range of records and resolve unexpected parameter prompts.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- An update query should assign a specific lookup value to coin records within a given ID range.
- Observed behavior
- A query that joins and filters the lookup table may update zero rows, while unrecognized field references can trigger parameter prompts.
Open the relevant tables in Access and confirm the exact table and field names. Check that 7 is a valid stored key in the lookup table and that CoinId is a numeric field.
Update the stored lookup key without joining the lookup table
Use this approach when the lookup field stores a numeric key and you want to assign that key to records in a specific ID range.
A lookup field can display descriptive text while storing a numeric key. Filtering the joined lookup table for the target value may select only records that already have that value.
In Access, select Create > Query Design, close the table-selection window if it appears, and switch to View > SQL View.
Enter: UPDATE [US Coins] SET [Issue] = 7 WHERE [CoinId] Between 525 And 595; Replace the table name, field names, key, or ID range if your database uses different values.
Select Run on the query toolbar and review the update confirmation before proceeding. The Between condition includes both 525 and 595.
Open the US Coins table in Datasheet View and inspect records in the specified CoinId range. The Issue lookup should display the description associated with key 7.

Review exported Access data in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface and Microsoft Office format compatibility. Export an Access table to Excel to review the data in WPS Spreadsheets; WPS does not run or fix Access update queries.

Frequently Asked Questions
Why does my update query affect zero rows?
A join or filter on the lookup table may select only records that already have the target value. Update the stored key directly in the main table and filter by the records you want to change.
Why does Access ask me to enter a parameter?
Access may interpret an unrecognized table or field reference as a parameter. Open the table in Design View and compare its field names with those in SQL View, including spelling and spaces.
Should I enter the lookup description or its numeric key?
For a lookup field that stores a numeric key, set the field to the key, such as 7. Check the lookup table to confirm that the key exists and corresponds to the desired description.
Can I open my Access database directly in WPS Office?
WPS Office does not run Access database queries. To review table data in WPS Spreadsheets, use Access's External Data > Export > Excel option, then open the exported workbook in WPS.




