Query works OK until a criteria is added

[EdAROC]

Creating a query to compile data for a report. There are two conditions when the data will be included in the report. I created a field that determines if the row should be included in the report:

INCLUDE_FLG: IIf([SumOfINVBQTYONHAND]>0 Or [QTY_TO_MFR]>0,"Y","N")
Therefore, the value is either a "Y" or an "N". At this point the query works OK.

> SumOfINVBQTYONHAND is a field created in a totalling query.

> QTY_TO_MFR is a field in this query defined as:
QTY_TO_MFR: IIf([ORDERS]![COMPLETION_FLG] Not In ("C","X") And [02 Tally Qty Mfrd]![SumOfITQUANTITY] Is Null,[ORDERS]![QTY_ORDERED],Null)

When I enter "Y" in the INCLUDE_FLG Criteria and run the query a parameter dialog box is displayed for QTY_TO_MFR.
All I wanted to do was filter out those rows that do not belong in the query.

Help!

 

[PHV]

QTY_TO_MFR isn't a field but an alias and I don't think you may use an alias in the criteria cell.
In fact, in your WHERE clause (in SQL view pane) you want something like this ?
WHERE (SumOfINVBQTYONHAND>0 OR (ORDERS.QTY_ORDERED>0 AND ORDERS.COMPLETION_FLG Not In ('C','X') AND [02 Tally Qty Mfrd].SumOfITQUANTITY Is Null))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

QTY_TO_MFR is a field alias and you cannot use a field alias to reference that field elsewhere in the query. You need to include the expression that is used to compute it rather than the alias name. Thus

INCLUDE_FLG: IIf([SumOfINVBQTYONHAND]>0 Or 
IIf([ORDERS]![COMPLETION_FLG] Not In ("C","X") And [02 Tally Qty Mfrd]![SumOfITQUANTITY] Is Null,[ORDERS]![QTY_ORDERED],Null)
>0,"Y","N")

 

[EdAROC]

Thanks for giving me direction...
I added the "Y" to the Criteria (and your reply sounded like you thought I was adding the QTY_TO_MFR to the criteria - I'm not), I get the following in SQL view (The ******* is inserted in the middle so you see the phrase defining QTY_TO_MFR, the second asterisks line precedes the phrase define INCLUDE_FLG):

SELECT ORDERS.ORDER_NO, ORDERS.CSCODE, ORDERS.COMPLETION_FLG, ORDERS.CUST_PO_NO, SPECS.CUST_IDENT, ORDERS.QTY_ORDERED, [02 Tally Qty Mfrd].SumOfITQUANTITY, [01 Tally QOH].D_QTY, [01 Tally QOH].C_QTY, [01 Tally QOH].O_QTY, [01 Tally QOH].SumOfINVBQTYONHAND,
*******
IIf([ORDERS]![COMPLETION_FLG] Not In ("C","X") And [02 Tally Qty Mfrd]![SumOfITQUANTITY] Is Null,[ORDERS]![QTY_ORDERED],Null) AS QTY_TO_MFR,
*******
IIf([SumOfINVBQTYONHAND]>0 Or [QTY_TO_MFR]>0,"Y","N") AS INCLUDE_FLG
FROM (ORDERS LEFT JOIN SPECS ON ORDERS.SPEC_NO = SPECS.SPEC_NO) LEFT JOIN ([01 Tally QOH] LEFT JOIN [02 Tally Qty Mfrd] ON [01 Tally QOH].ORDER_NO = [02 Tally Qty Mfrd].ORDER_NO) ON ORDERS.ORDER_NO = [01 Tally QOH].ORDER_NO
WHERE (((ORDERS.CSCODE)="138") AND ((ORDERS.COMPLETION_FLG)<>"X") AND ((IIf([SumOfINVBQTYONHAND]>0 Or [QTY_TO_MFR]>0,"Y","N"))="Y"));

 

[EdAROC]

I understand now!!!
PHV - thanks for the guidance to look at the SQL view.
Golom - I didn't get what you explained at first. Went back and replaced the QTY_TO_MFR with the expression itself - it works!!!

Thank you! For time and effort and explaining.

 

[PHV]

And what about this where clause ?
WHERE ORDERS.CSCODE='138' AND ORDERS.COMPLETION_FLG<>'X'
AND ([01 Tally QOH].SumOfINVBQTYONHAND>0 OR (ORDERS.QTY_ORDERED>0 AND ORDERS.COMPLETION_FLG Not In ('C','X') AND [02 Tally Qty Mfrd].SumOfITQUANTITY Is Null)) ;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886