Others

Fix a Microsoft Access Update Query That Updates Zero Rows

Bushra ParveenBushra Parveen Nov 20, 2026 869 views

Question details

Fix an Access UPDATE query that matches no records when it uses a value from an unbound form control.

Fix a Microsoft Access Update Query That Updates Zero Rows
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.
Before you start

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.

Solution 1Recommended

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.

1
Check the field type

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.

2
Update the form's VBA code

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

3
Inspect and test the query

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.

Quote text criteria and inspect the generated Access SQL
For numeric WorkOrder fields: Do not use the text-quoting example for a numeric field. Validate and convert the form value to the field's numeric type, then build the criterion without text quotation marks. Trim removes only leading and trailing spaces, not spaces within a value.
Free Microsoft Office alternative

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.

Review and organize work-order data in WPS SpreadsheetOpen and edit Microsoft Excel XLSX filesWork with Word and PowerPoint formats such as DOCX and PPTXUse familiar editing tools in a lightweight office suite
microsoft office alternative - wps office

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.