Select Reccords based on Expression Results

  • Thread starter Thread starter Marktheshark
  • Start date Start date
M

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.
 
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.
Click to expand...

Where did you put the expression? Please open the query in SQL view and post
it here.
 
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;
 
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;



John W. Vinson said:
Where did you put the expression? Please open the query in SQL view and post
it here.
Click to expand...
Click to expand...
 
Back
Top