MS Access SQL Query not recognizing Criteria parameter

Question

MS Access SQL Query not recognizing Criteria parameter

Anonymous

I am working on a specific SQL query to filter a list of data that does not contain a specific value. I am trying to use (in the Design view) a criteria that is: <> "SLC-DC". When the query is run the "does not equal" statement seems to be ignored. This is what my full SQL query looks like:

SELECT [Key Inventory].ID, [Key Inventory].[Authorization #], [Key Inventory].[Key Name], [Key Inventory].[Serial Number], [Key Inventory].[Key Use], [Key Inventory].[Key Type], [Key Inventory].[Key Type Code], [Key Inventory].[Copy Holder], [Key Inventory].Location, [Key Inventory].[Comp1 Tb (D1)], [Key Inventory].[Comp2 Tb (D2)], [Key Inventory].[Comp3 Tb (D3)]

FROM [Key Inventory]

WHERE ((([Key Inventory].[Copy Holder])="BU2") AND (([Key Inventory].Location)<>"SLC-DC3"));

Some background - this [Key Inventory].Location field has a source that is a combobox pulling information from another table to make this a drop down field.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

1 additional answer

Answer 1

The likelihood is that the value of the Location foreign key column in the Key Inventory table is a long integer data type referencing the numeric primary key of the Locations table. The combo box has probably been set up with a RowSource property such as:

SELECT LocationID, Location FROM Locations ORDER BY :Location;

and the LocationID column has been hidden by setting the first dimension of the combo box's ColumnWidths property to zero

If this is the case, to restrict your query on the Location column it will be necessary to do so on the numeric value, not the string expression.

In an operational database it is unusual for a query to be restricted on a literal value. More commonly the parameter will be a reference to an unbound control in a dialogue form. In this case the control would be a combo box, set up in exactly the same way as the bound combo box, but with an empty ControlSource property. It would then be referenced in the query like this:

<>Forms![FormNameGoesHere]![UnboundComboBoxNameGoesHere]

If you do wish to use a literal value, however, then it would be an integer number, and not enclosed in quotes characters, e.g.

<> 42

Answer 2

The likelihood is that the value of the Location foreign key column in the Key Inventory table is a long integer data type referencing the numeric primary key of the Locations table. The combo box has probably been set up with a RowSource property such as:

   SELECT LocationID, Location FROM Locations ORDER BY :Location;

and the LocationID column has been hidden by setting the first dimension of the combo box's ColumnWidths property to zero

If this is the case, to restrict your query on the Location column it will be necessary to do so on the numeric value, not the string expression.

In an operational database it is unusual for a query to be restricted on a literal value. More commonly the parameter will be a reference to an unbound control in a dialogue form. In this case the control would be a combo box, set up in exactly the same way as the bound combo box, but with an empty ControlSource property. It would then be referenced in the query like this:

    <>Forms![FormNameGoesHere]![UnboundComboBoxNameGoesHere]

If you do wish to use a literal value, however, then it would be an integer number, and not enclosed in quotes characters, e.g.

    <> 42

I found out after this was pointed out that a whole bunch of my column values were all messed up. Once I got all of the data types correct, I was able to run the query again and use a numeric value for the choice that I wanted and my SQL query turned out correctly and showed what I wanted to see.

Share via

MS Access SQL Query not recognizing Criteria parameter

1 additional answer