Others

Fix a Not-Equal Criterion in a Microsoft Access Location Query

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 869 views

Question details

The user needs an Access query to exclude a location selected through a lookup or combo box.

Fix a Not-Equal Criterion in a Microsoft Access Location Query
Product
Microsoft Access
Device & OS
not provided
Scenario
A location combo box displays a name while the underlying Location field stores a numeric foreign key.
Observed behavior
A not-equal criterion based on the displayed location text does not produce the expected query results.
Before you start

Identify the numeric key for the location you want to exclude and check which column the combo box stores as its value. Open the query and the related table in Design View so you can inspect the fields and their data types.

Solution 1Recommended

Filter the stored location key in Access Query Design

Use this approach when the Location field stores a numeric key rather than the displayed location name.

1
Check the stored key

Open the location lookup table and find the numeric ID associated with the location to exclude, such as 42.

2
Enter the criterion

Open the query in Design View. In the Criteria row under the stored Location field, enter <> 42, replacing 42 with the correct key.

3
Check data types

Open the related tables in Design View and confirm that the foreign key and referenced key have compatible data types and field sizes. For example, a foreign key referencing an AutoNumber key is typically Number with the Long Integer field size.

4
Run the query

Return to Query Design and select Run. Confirm that records with the specified location key are excluded.

Filter the stored location key in Access Query Design
Records with no location: A not-equal criterion does not include Null values. To include records with no location, use <> 42 Or Is Null in the Location field's Criteria row.
Free Microsoft Office alternative

Review exported Access data in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with exported data. Open Excel-format exports in WPS Spreadsheets for a straightforward transition; WPS does not fix Access queries or replace an Access database.

Filter exported location data in a spreadsheetOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my location name work in the criterion?

The lookup may display a name while the Location field stores its numeric key. In Query Design, enter the criterion against the stored key, such as <> 42.

How can I exclude the location chosen in a combo box?

In the Location field's Criteria row, enter <> Forms![FormName]![ControlName]. Keep the form open, select a location, and ensure the combo box's bound value is the numeric key.

Why are records with no location missing from my results?

A not-equal comparison does not include Null values. To include them, enter a criterion such as <> 42 Or Is Null under the Location field.

Which data types should the location key fields use?

The foreign key and referenced key should have compatible types and field sizes. For example, a foreign key referencing an AutoNumber key is typically a Number field with the Long Integer field size. Check these settings in each table's Design View.