Fix Microsoft Access Query Results for New Form Records
Question details
Identify why new records entered through a form are missing from an Access query or appear when they should not match the search.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A search form uses a query to retrieve records from a table populated through a data-entry form.
- Observed behavior
- Six new records with null Name values appear unexpectedly in search results, while other null Name values do not appear as expected.
Open the Cases table and inspect the Name values in the affected records. Save the current record in the data-entry form, then make a copy of CasesQ so you can test its criteria without changing the working query.
Test query criteria and null conditions one at a time
Use this method to find the condition that excludes valid records or returns unwanted null Name records.
In the Navigation Pane, right-click the copied CasesQ query and select Design View.
Clear the Criteria and Or rows for the fields being tested, and remove any query-level filters. Click Run and check whether the new records appear.
Return to Design View and add the original criteria back one at a time, clicking Run after each change. Note which condition causes records to disappear or appear unexpectedly.
In the Name column, review every Criteria and Or row containing Is Null or Is Not Null. Criteria on the same row are combined with AND; separate Or rows are alternatives. Place related conditions on the intended rows so an Is Null condition does not bypass other search criteria.
Once the copied query returns the expected records, apply the tested criteria changes to CasesQ and run the search form again.
Review joins and search-form references
Check these settings if the criteria test does not explain why records are missing or unexpectedly included.
Review exported Access records in WPS Spreadsheets
WPS Office is a free, lightweight alternative for working with exported data in a familiar spreadsheet interface. Export an Access table or query to Excel, then open it in WPS Spreadsheets for review. WPS does not repair Access queries or replace Access forms.
Frequently Asked Questions
Why do records with a blank Name appear in my search results?
In query Design View, check the Name column for Is Null on an Or row. That row may allow null-name records to match independently of criteria on other rows.
How do I find the criterion that is excluding a new record?
Open a copy of the query in Design View, temporarily clear its criteria, and click Run. Restore each condition one at a time until the record disappears.
Can a join prevent a new record from appearing?
Yes. An inner join returns records only when there is a matching record in both joined tables. In Design View, double-click the join line to review its join properties.
Why does my search form show old results after I add a record?
Save the new record in the data-entry form, then rerun the search using the Run Query button. If the results form stays open, it may need to be requeried.
Can WPS Office fix my Access query?
No. Edit query criteria, joins, and form references in Microsoft Access. To review the data in WPS Spreadsheets, export the Access table or query to an Excel file and open that file in WPS.
