Limit the results from a calculated field in a query

H

Helen Smith

I have a field in a query which calculates items in stock ie: Balance:
[Pallets Received]-[Qty Despatched] but I only want it to return values
greater than 0. If I enter >0 in the criteria row then nothing is returned.
Where should I enter the criteria?
 
D

Dorian

You need a WHERE clause
WHERE [Pallets Received]-[Qty Despatched] > 0
Go into SQL mode and add this clause.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
K

KARL DEWEY

Check the properties of field [Pallets Received] and [Qty Despatched] to be
sure they are number fields and not text.
Also maybe use <>0 as your criteria.
 
J

John Spencer

That should work as long as both fields have a value and the values are numeric

Optionally, you could try
Field: [Pallets Received]
Criteria: > Nz([Qty Despatched],0)

If this does not answer your question, please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

Jonathan Kim

I have a field in a query which calculates items in stock ie: Balance:
[Pallets Received]-[Qty Despatched] but I only want it to return values
greater than 0. If I enter >0 in the criteria row then nothing is returned.
Where should I enter the criteria?
Click to expand...

It sounds that you want Balance to display nothing if the value of
Balance is 0 or negative, but the record should still appear on the
query result. Please correct me if I am wrong.
Then, build as below in Field
Balance: iif(([Pallets Received]-[Qty Despatched]) <= 0, Null,
[Pallets Received]-[Qty Despatched])
 
H

Helen Smith

Thank you all for your advice, I am still having trouble, however I did not
give you the correct info: The Qty Despatched field is a sum, the SQL view
looks like this:

SELECT Products.Code, Balances.[CT No], Balances.[Product Description],
Balances.[Pallets Received], Sum(Balances.QtyOut) AS SumOfQtyOut,
Balances.[Sell By Date], [Pallets Received]-[SumOfQtyOut] AS Balance,
Balances.[No Cases Recd], Balances.Comments, Balances.[Date In]
FROM Products INNER JOIN Balances ON Products.Product = Balances.[Product
Description]
GROUP BY Products.Code, Balances.[CT No], Balances.[Product Description],
Balances.[Pallets Received], Balances.[Sell By Date], Balances.[No Cases
Recd], Balances.Comments, Balances.[Date In];

When I put >0 In the criteria line for Balances then a box comes up which
says

Enter Paramater Value for SumOfQtyOut.

Any ideas gratefully received!



Jonathan Kim said:
I have a field in a query which calculates items in stock ie: Balance:
[Pallets Received]-[Qty Despatched] but I only want it to return values
greater than 0. If I enter >0 in the criteria row then nothing is returned.
Where should I enter the criteria?
Click to expand...

It sounds that you want Balance to display nothing if the value of
Balance is 0 or negative, but the record should still appear on the
query result. Please correct me if I am wrong.
Then, build as below in Field
Balance: iif(([Pallets Received]-[Qty Despatched]) <= 0, Null,
[Pallets Received]-[Qty Despatched])
Click to expand...
 
J

John Spencer

The HAVING clause is not aware of the existence of the calculation you have
aliased as SubOfQtyOut. Instead of using that alias use the actual
calculation again.

SELECT Products.Code, Balances.[CT No]
, Balances.[Product Description]
, Balances.[Pallets Received]
, Sum(Balances.QtyOut) AS SumOfQtyOut
, Balances.[Sell By Date]
, [Pallets Received]-Sum(Balances.QtyOut) AS Balance
, Balances.[No Cases Recd]
, Balances.Comments
, Balances.[Date In]
FROM Products INNER JOIN Balances
ON Products.Product = Balances.[Product Description]
GROUP BY Products.Code
, Balances.[CT No]
, Balances.[Product Description]
, Balances.[Pallets Received]
, Balances.[Sell By Date]
, Balances.[No Cases Recd]
, Balances.Comments
, Balances.[Date In]
HAVING [Pallets Received] - Sum(Balances.QtyOut) > 0


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Helen said:
Thank you all for your advice, I am still having trouble, however I did not
give you the correct info: The Qty Despatched field is a sum, the SQL view
looks like this:

SELECT Products.Code, Balances.[CT No], Balances.[Product Description],
Balances.[Pallets Received], Sum(Balances.QtyOut) AS SumOfQtyOut,
Balances.[Sell By Date], [Pallets Received]-[SumOfQtyOut] AS Balance,
Balances.[No Cases Recd], Balances.Comments, Balances.[Date In]
FROM Products INNER JOIN Balances ON Products.Product = Balances.[Product
Description]
GROUP BY Products.Code, Balances.[CT No], Balances.[Product Description],
Balances.[Pallets Received], Balances.[Sell By Date], Balances.[No Cases
Recd], Balances.Comments, Balances.[Date In];

When I put >0 In the criteria line for Balances then a box comes up which
says

Enter Paramater Value for SumOfQtyOut.

Any ideas gratefully received!



Jonathan Kim said:
I have a field in a query which calculates items in stock ie: Balance:
[Pallets Received]-[Qty Despatched] but I only want it to return values
greater than 0. If I enter >0 in the criteria row then nothing is returned.
Where should I enter the criteria?
Click to expand...
It sounds that you want Balance to display nothing if the value of
Balance is 0 or negative, but the record should still appear on the
query result. Please correct me if I am wrong.
Then, build as below in Field
Balance: iif(([Pallets Received]-[Qty Despatched]) <= 0, Null,
[Pallets Received]-[Qty Despatched])
Click to expand...
Click to expand...
 
H

Helen Smith

Thank you so much. This has worked and i am EXTREMELY HAPPY!!!

John Spencer said:
The HAVING clause is not aware of the existence of the calculation you have
aliased as SubOfQtyOut. Instead of using that alias use the actual
calculation again.

SELECT Products.Code, Balances.[CT No]
, Balances.[Product Description]
, Balances.[Pallets Received]
, Sum(Balances.QtyOut) AS SumOfQtyOut
, Balances.[Sell By Date]
, [Pallets Received]-Sum(Balances.QtyOut) AS Balance
, Balances.[No Cases Recd]
, Balances.Comments
, Balances.[Date In]
FROM Products INNER JOIN Balances
ON Products.Product = Balances.[Product Description]
GROUP BY Products.Code
, Balances.[CT No]
, Balances.[Product Description]
, Balances.[Pallets Received]
, Balances.[Sell By Date]
, Balances.[No Cases Recd]
, Balances.Comments
, Balances.[Date In]
HAVING [Pallets Received] - Sum(Balances.QtyOut) > 0


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Helen said:
Thank you all for your advice, I am still having trouble, however I did not
give you the correct info: The Qty Despatched field is a sum, the SQL view
looks like this:

SELECT Products.Code, Balances.[CT No], Balances.[Product Description],
Balances.[Pallets Received], Sum(Balances.QtyOut) AS SumOfQtyOut,
Balances.[Sell By Date], [Pallets Received]-[SumOfQtyOut] AS Balance,
Balances.[No Cases Recd], Balances.Comments, Balances.[Date In]
FROM Products INNER JOIN Balances ON Products.Product = Balances.[Product
Description]
GROUP BY Products.Code, Balances.[CT No], Balances.[Product Description],
Balances.[Pallets Received], Balances.[Sell By Date], Balances.[No Cases
Recd], Balances.Comments, Balances.[Date In];

When I put >0 In the criteria line for Balances then a box comes up which
says

Enter Paramater Value for SumOfQtyOut.

Any ideas gratefully received!



Jonathan Kim said:
On Oct 1, 9:36 am, Helen Smith <[email protected]>
wrote:
I have a field in a query which calculates items in stock ie: Balance:
[Pallets Received]-[Qty Despatched] but I only want it to return values
greater than 0. If I enter >0 in the criteria row then nothing is returned.
Where should I enter the criteria?
It sounds that you want Balance to display nothing if the value of
Balance is 0 or negative, but the record should still appear on the
query result. Please correct me if I am wrong.
Then, build as below in Field
Balance: iif(([Pallets Received]-[Qty Despatched]) <= 0, Null,
[Pallets Received]-[Qty Despatched])
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dynamic calculated field in query 3
Use In() Expression as Query criteria with guts from Function 1
Criteria in calculated field 3
New User Query Question - really need some help!! 2
Query Field limit 2
Reference Calculated Field 2
#Error in calculated field 3
Parameter query not returning all results 4

Top