Others

Fix a Microsoft Access Update Query for a Lookup Field

Steve KSteve K Nov 18, 2026 868 views

Question details

Update an Access lookup field for a range of records and resolve unexpected parameter prompts.

Fix a Microsoft Access Update Query for a Lookup Field
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.
Before you start

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.

Solution 1Recommended

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.

1
Open SQL View

In Access, select Create > Query Design, close the table-selection window if it appears, and switch to View > SQL View.

2
Enter the update query

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.

3
Run the query

Select Run on the query toolbar and review the update confirmation before proceeding. The Between condition includes both 525 and 595.

4
Check the results

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.

Update the stored lookup key without joining the lookup table
Check parameter prompts: If Access prompts for a parameter, check the spelling of [US Coins], [Issue], and [CoinId] against the actual table and field names. Access may treat an unrecognized name as a parameter; entering 7 or the range at the prompt does not correct the reference.
Free Microsoft Office alternative

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.

Review exported lookup-field data in a spreadsheetOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface for a straightforward transitionWork with a lightweight, free office suite
microsoft office alternative - wps office

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.