Fix a Microsoft Access Update Query That Updates Zero Rows
Question details
Fix an Access UPDATE query that matches no records when it uses a value from an unbound form control.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A form supplies a WorkOrder value dynamically to an UPDATE query for the Inspections table.
- Observed behavior
- The query updates zero records when the WorkOrder value is entered through the form.
Open the Inspections table in Design View and check the WorkOrder field's data type. Identify the form control that supplies the value and check whether the entered value contains leading or trailing spaces.
Quote text criteria and inspect the generated Access SQL
Use this approach when WorkOrder is a text field and the query builds its criteria from an unbound form control.
Text criteria need quotation marks in Access SQL. Trim leading and trailing spaces from the input, and escape any apostrophes before adding the value to the SQL string.
In the Navigation Pane, right-click Inspections and select Design View. Find WorkOrder and confirm that its data type is Short Text or another text type before using the code below.
Open the form in Design View, select the button that runs the update, and open its On Click event. In the VBA editor, use the following code inside the event procedure, replacing WO if your control has a different name: Dim db As DAO.Database Dim workOrderValue As String Dim sql As String workOrderValue = Trim$(Nz(Me!WO.Value, "")) If Len(workOrderValue) = 0 Then MsgBox "Enter a WorkOrder value." Exit Sub End If sql = "UPDATE Inspections SET Complete=True WHERE WorkOrder='" & Replace(workOrderValue, "'", "''") & "'" Debug.Print sql Set db = CurrentDb db.Execute sql, dbFailOnError Debug.Print "Rows updated: " & db.RecordsAffected
Run the form button, then press Ctrl+G in the VBA editor to open the Immediate window. Check the printed SQL and row count. If no rows match, compare the quoted WorkOrder value with the stored value in the Inspections table, including any spaces.

Work with Office files in WPS Office
WPS Office is a free, lightweight alternative for working with documents and spreadsheets in a familiar interface. Its Microsoft Office format compatibility makes it easy to bring existing files into your workflow. It does not run Microsoft Access databases or fix Access UPDATE queries.

Frequently Asked Questions
Why does my Access update query run without changing any rows?
The WHERE criterion may not match a stored value. Check the WorkOrder field type in the table's Design View, then use Debug.Print and Ctrl+G in the VBA editor to inspect the generated SQL.
Should a WorkOrder value be enclosed in single quotes?
Enclose it in single quotes when WorkOrder is a text field. For a numeric field, validate the input and use a numeric criterion without text quotation marks.
How can I check for spaces in the form value?
Inspect the value entered in the WO control and the value stored in the Inspections table. Use Trim$ on the form value to remove leading and trailing spaces; it does not remove spaces within the value or change stored records.
What if a WorkOrder value contains an apostrophe?
When building a text SQL string, escape each apostrophe by doubling it. In VBA, Replace(workOrderValue, "'", "''") does this before the value is added to the WHERE clause.




