Query To Retrieve Less Than or Equal To

[Guest]

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.

 

[MGFoster]

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])));

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

 

[Guest]

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

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])));

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

 

[Rick Brandt]

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

Are the fields definitely using a numeric data type and not Text?

 

[Guest]

Yes they are numeric, not text. Although the units on hand is a calculation.
A sum of several bin locations - would that make a difference?

Thank you.

 

[Rick Brandt]

Yes they are numeric, not text. Although the units on hand is a
calculation. A sum of several bin locations - would that make a
difference?

It shouldn't. Do the fields in the datasheet justify to the left or right? If
something is inadvertantly changing one of your values to Text that will be a
clue since Text fields align left and numbers align right. Using the Nz()
function can sometimes do this (he said while ducking his head).

(inside joke, sorry)

 

[Guest]

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.

 

[Rick Brandt]

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?

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

 

[Guest]

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)

 

[Rick Brandt]

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)

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?