How can I filter for a field being a lower numeric value?

[Sharnete]

Hello, very limited Access experience I'm afraid. Need a bit of help...

How can I do a filter search within Access where I need to filter for one field being a lower numeric value than another? I have a table that contains the fields "Quantity in Stock" & "Reorder Level". How can I filter for just the records where the Reorder level is less than or equal to the number in stock? (If I try "<= Reorder level" in the "Qty" field, I just get a data mismatch!).

Thanks in advance!

 

[DCrake]

Simple Software Solutions

Two possible reasons for the error

1. The sysntax you are using is incorrect
2. You are trying to evaluate a text field

Solutions:

The correct syntax for for your query is <= [Reorder Level]

or

Create a new column in your query with the following syntax

Reorder:Iff([Reorder Level] <=[Quantity In Stock],True,False)

Then in your criteria section use True as the criterion.

Recommendations:
Try to avoid using spaces in your field names as this means encompassing them in square brackets [].

CodeMaster:

 

[Sharnete]

Brilliant! Many thanks! (I'd been using "()" instead of "[]".
Thanks.

 

[DCrake]

Simple Software Solutions

Often the easiest of mistakes are the hardest to detect, you cannot see the trees for the woods.

Glad to be of help