Query To Retrieve Less Than or Equal To

J

Jacine

I am having trouble figuring what is wrong with this query - I'm not very
good at queries.

I have a parts and supplies table and each item has a minimum and maximum.
There is another field for units on hand. I tried to run a query that would
let me know which items are at the minimum or less than as well as when at
the maximum or greater than from the units on hand.

This is the SQL text that I tried, but I don't get any results:

SELECT [Parts and Supplies].PartNumber, [Parts and Supplies].UnitsOnHand,
[Parts and Supplies].Minimum, [Parts and Supplies].Maximum

FROM [Parts and Supplies]

WHERE ((([Parts and Supplies].UnitsOnHand)<=([Minimum]) Or ([Parts and
Supplies].UnitsOnHand)>=([Maximum])));

Thank you.
 
M

MGFoster

Jacine said:
I am having trouble figuring what is wrong with this query - I'm not very
good at queries.

I have a parts and supplies table and each item has a minimum and maximum.
There is another field for units on hand. I tried to run a query that would
let me know which items are at the minimum or less than as well as when at
the maximum or greater than from the units on hand.

This is the SQL text that I tried, but I don't get any results:

SELECT [Parts and Supplies].PartNumber, [Parts and Supplies].UnitsOnHand,
[Parts and Supplies].Minimum, [Parts and Supplies].Maximum

FROM [Parts and Supplies]

WHERE ((([Parts and Supplies].UnitsOnHand)<=([Minimum]) Or ([Parts and
Supplies].UnitsOnHand)>=([Maximum])));
Click to expand...

It should work. Do you have any records that fit the criteria?
 
J

Jacine

Yes I do but I get zero results. That's what I don't understand.

MGFoster said:
Jacine said:
I am having trouble figuring what is wrong with this query - I'm not very
good at queries.

I have a parts and supplies table and each item has a minimum and maximum.
There is another field for units on hand. I tried to run a query that would
let me know which items are at the minimum or less than as well as when at
the maximum or greater than from the units on hand.

This is the SQL text that I tried, but I don't get any results:

SELECT [Parts and Supplies].PartNumber, [Parts and Supplies].UnitsOnHand,
[Parts and Supplies].Minimum, [Parts and Supplies].Maximum

FROM [Parts and Supplies]

WHERE ((([Parts and Supplies].UnitsOnHand)<=([Minimum]) Or ([Parts and
Supplies].UnitsOnHand)>=([Maximum])));
Click to expand...

It should work. Do you have any records that fit the criteria?
Click to expand...
 
J

Jacine

I know why I am not getting any results. The Units On Hand is a sum on a
form that I made from the table. When I looked at the table, it does not
show that amount - only on the form. So I put the expression I used to get
the amount on the form, but know it shows all the part numbers whether they
have any units on hand or not.

How do I have it pull only the records that apply to the minimum or maximum?

Thank you.
 
R

Rick Brandt

Jacine said:
I know why I am not getting any results. The Units On Hand is a sum
on a form that I made from the table. When I looked at the table, it
does not show that amount - only on the form. So I put the
expression I used to get the amount on the form, but know it shows
all the part numbers whether they have any units on hand or not.

How do I have it pull only the records that apply to the minimum or
maximum?
Click to expand...

Add the calculation that you are doing on the form into your query.
 
J

Jacine

I did, maybe I'm not doing this right. This is the expression/calculation I
entered:

Expr1:
([UnitsBinLocation1])+([UnitsBinLocation2])+([UnitsBinLocation3])+([UnitsBinLocation4])+([UnitsBinLocation5])+([UnitsBinLocation6])+([UnitsBinLocation7])+([UnitsBinLocation8])+([UnitsBinLocation9])+([UnitsBinLocation10])+([UnitsBinLocation11])+([UnitsBinLocation12])

with the following criteria below:

<=([Minimum]) Or >=([Maximum])

I get all the parts and supplies whether they fall within that criteria or
not. Even if they have a 0 value (No minimum or maximum entered and/or no
quantities)
 
R

Rick Brandt

Jacine said:
I did, maybe I'm not doing this right. This is the
expression/calculation I entered:

Expr1:
([UnitsBinLocation1])+([UnitsBinLocation2])+([UnitsBinLocation3])+([UnitsBinLocation4])+([UnitsBinLocation5])+([UnitsBinLocation6])+([UnitsBinLocation7])+([UnitsBinLocation8])+([UnitsBinLocation9])+([UnitsBinLocation10])+([UnitsBinLocation11])+([UnitsBinLocation12])

with the following criteria below:

<=([Minimum]) Or >=([Maximum])

I get all the parts and supplies whether they fall within that
criteria or not. Even if they have a 0 value (No minimum or maximum
entered and/or no quantities)
Click to expand...

As a side issue it looks like your design is not optimum. Multiple fields with
names like...

UnitsBinLocation1, UnitsBinLocation2, etc..

....usually indicate a problem. Those kinds of entries are more properly stored
as separate records in a separate Locations table. Then it is a lot easier to
aggregate them.

Do all fields have a number (even zero) or can some fields be Null? If any can
be Null then you need to wrap all of them in the Nz() function.

What does the output of Expr1 look like in your query?
 
Top