Select Reccords based on Expression Results

[Marktheshark]

Hi,

I have created a Select Record Access Query and have and Expression that
calculates a value based on two of the table fields. This caluclation is okay
when I review the output but I want to only select records with a certain
value (i.e. <50). The problem is that when I enter 50 in the Criteria cell
under the Expression and run the Query I get an Enter Parameter Value box!

What am I donig wrong?

I would be greatfull for any help.

 

[bhicks11 via AccessMonster.com]

Did you put < 50? Is it a number field? Maybe if you post the SQL?

Bonnie
http://www.dataplus-svc.com

 

[John W. Vinson]

Hi,

I have created a Select Record Access Query and have and Expression that
calculates a value based on two of the table fields. This caluclation is okay
when I review the output but I want to only select records with a certain
value (i.e. <50). The problem is that when I enter 50 in the Criteria cell
under the Expression and run the Query I get an Enter Parameter Value box!

What am I donig wrong?

I would be greatfull for any help.

Where did you put the expression? Please open the query in SQL view and post
it here.

 

[Marktheshark]

Thanks for your response. The expression is in the feild cell of the Query
Design View and the value comparision in the Criteria cell. Here is the SQL:

SELECT F4801.WAMCU AS Branch, F4801.WADOCO AS [WO Number], F4801.WALITM AS
[Item Number], F4801.WAUORG AS [Order Qty], F4801.WASOQS AS [Qty Completed],
F4801.WASRST AS [WO Status], F4801.WAUSER AS Originator, (([Qty
Completed]-[Order Qty])/[Order Qty]) AS Expr1
FROM F4801
GROUP BY F4801.WAMCU, F4801.WADOCO, F4801.WALITM, F4801.WAUORG,
F4801.WASOQS, F4801.WASRST, F4801.WAUSER
HAVING (((F4801.WAMCU)=" 104910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 107910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 144910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 104910001") AND ((F4801.WASRST)="95"))
ORDER BY F4801.WAMCU, F4801.WADOCO;

 

[John Spencer]

A group by (Totals) query is unnecessary since you are not aggregating
(combining) any of the fields.

Try the following
-- Replace the reference to the field alias with the actual field names.
-- Drop the Group by
-- Change the HAVING clause to a WHERE clause
-- Add DISTINCT to the query if you are getting duplicate values and
want them reduced to one row.

SELECT DISTINCT F4801.WAMCU AS Branch
, F4801.WADOCO AS [WO Number]
, F4801.WALITM AS [Item Number]
, F4801.WAUORG AS [Order Qty]
, F4801.WASOQS AS [Qty Completed]
, F4801.WASRST AS [WO Status]
, F4801.WAUSER AS Originator
, (([WASOQS]-[WAUORG])/[WAUORG]) AS Expr1
FROM F4801
WHERE F4801.WAMCU IN (" 104910201"," 107910201"," 144910201", "
104910001")

AND F4801.WASRST="95"

AND ([WASOQS]-[WAUORG])/[WAUORG] < 50

ORDER BY F4801.WAMCU, F4801.WADOCO

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

Thanks for your response. The expression is in the feild cell of the Query
Design View and the value comparision in the Criteria cell. Here is the SQL:

SELECT F4801.WAMCU AS Branch, F4801.WADOCO AS [WO Number], F4801.WALITM AS
[Item Number], F4801.WAUORG AS [Order Qty], F4801.WASOQS AS [Qty Completed],
F4801.WASRST AS [WO Status], F4801.WAUSER AS Originator, (([Qty
Completed]-[Order Qty])/[Order Qty]) AS Expr1
FROM F4801
GROUP BY F4801.WAMCU, F4801.WADOCO, F4801.WALITM, F4801.WAUORG,
F4801.WASOQS, F4801.WASRST, F4801.WAUSER
HAVING (((F4801.WAMCU)=" 104910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 107910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 144910201") AND ((F4801.WASRST)="95")) OR
(((F4801.WAMCU)=" 104910001") AND ((F4801.WASRST)="95"))
ORDER BY F4801.WAMCU, F4801.WADOCO;

Where did you put the expression? Please open the query in SQL view and post
it here.