Criteria question

[Mark]

Hello all,

I have a query that runs great, until I use the where clause (WHERE
((([pickpro]-[sumofqty])<>0)). When I add this it pops up the input box
for “Pickproâ€.

Here are the fields in play and below is all of the SQL.

PickPro: IIf(IsNull([qty]),0,[qty])

Provia Qty: SumOfQTY

Variance: [pickpro]-[sumofqty]
<> 0

SELECT qrProTotal.SKU, tbSkuSum.Qty, IIf(IsNull([qty]),0,[qty]) AS PickPro,
qrProTotal.SumOfQTY AS [Provia Qty], tbSkuSum.Desc, [pickpro]-[sumofqty] AS
Variance, IIf([variance]<"0",[variance]*-1,0) AS Low,
IIf([variance]>"0",[variance],0) AS High, tbSkuSum.date
FROM tbSkuSum RIGHT JOIN qrProTotal ON tbSkuSum.Item = qrProTotal.SKU
WHERE ((([pickpro]-[sumofqty])<>0));

 

[Duane Hookom]

I try to avoid using a calculated column as an expression in another
calculation.
This expression
IIf(IsNull([qty]),0,[qty])
Can be replaced by
Nz([qty]),0)

Also, you are comparing a numeric expression to a string <"0" and >"0". Try
something like:

SELECT qrProTotal.SKU, tbSkuSum.Qty, Nz([qty]),0) AS PickPro,
qrProTotal.SumOfQTY AS [Provia Qty], tbSkuSum.Desc, Nz([qty]),0)-[sumofqty]
AS
Variance, IIf((Nz([qty]),0)-[sumofqty])<0,(Nz([qty]),0)-[sumofqty])*-1,0) AS
Low,
IIf(Nz([qty]),0)-[sumofqty])>0,Nz([qty]),0)-[sumofqty],0) AS High,
tbSkuSum.date
FROM tbSkuSum RIGHT JOIN qrProTotal ON tbSkuSum.Item = qrProTotal.SKU
WHERE (((Nz([qty]),0)-[sumofqty])<>0));

 

[Mark]

Thanks! That works great!

I try to avoid using a calculated column as an expression in another
calculation.
This expression
IIf(IsNull([qty]),0,[qty])
Can be replaced by
Nz([qty]),0)

Also, you are comparing a numeric expression to a string <"0" and >"0". Try
something like:

SELECT qrProTotal.SKU, tbSkuSum.Qty, Nz([qty]),0) AS PickPro,
qrProTotal.SumOfQTY AS [Provia Qty], tbSkuSum.Desc, Nz([qty]),0)-[sumofqty]
AS
Variance, IIf((Nz([qty]),0)-[sumofqty])<0,(Nz([qty]),0)-[sumofqty])*-1,0) AS
Low,
IIf(Nz([qty]),0)-[sumofqty])>0,Nz([qty]),0)-[sumofqty],0) AS High,
tbSkuSum.date
FROM tbSkuSum RIGHT JOIN qrProTotal ON tbSkuSum.Item = qrProTotal.SKU
WHERE (((Nz([qty]),0)-[sumofqty])<>0));

--
Duane Hookom
MS Access MVP

Hello all,

I have a query that runs great, until I use the where clause (WHERE
((([pickpro]-[sumofqty])<>0)). When I add this it pops up the input box
for "Pickpro".

Here are the fields in play and below is all of the SQL.

PickPro: IIf(IsNull([qty]),0,[qty])

Provia Qty: SumOfQTY

Variance: [pickpro]-[sumofqty]
<> 0

SELECT qrProTotal.SKU, tbSkuSum.Qty, IIf(IsNull([qty]),0,[qty]) AS
PickPro,
qrProTotal.SumOfQTY AS [Provia Qty], tbSkuSum.Desc, [pickpro]-[sumofqty]
AS
Variance, IIf([variance]<"0",[variance]*-1,0) AS Low,
IIf([variance]>"0",[variance],0) AS High, tbSkuSum.date
FROM tbSkuSum RIGHT JOIN qrProTotal ON tbSkuSum.Item = qrProTotal.SKU
WHERE ((([pickpro]-[sumofqty])<>0));