Fix a Not-Equal Criterion in a Microsoft Access Location Query
Question details
The user needs an Access query to exclude a location selected through a lookup or combo box.

- 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.
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.
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.
Open the location lookup table and find the numeric ID associated with the location to exclude, such as 42.
Open the query in Design View. In the Criteria row under the stored Location field, enter <> 42, replacing 42 with the correct key.
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.
Return to Query Design and select Run. Confirm that records with the specified location key are excluded.

Exclude the location selected on an Access form
Use a form control as the criterion when the excluded location should change with the user's selection.
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.

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.




