Limit the results from a calculated field in a query

  • Thread starter Thread starter Helen Smith
  • Start date Start date
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?
 
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".
 
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.
 
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
 
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])
 
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...
 
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...
 
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...
 
Back
Top