IIf statement in query criteria

  • Thread starter Thread starter Access WannaBe
  • Start date Start date
A

Access WannaBe

I have a query that returns sales history for a range of inventory items. I
have a form that allows me to enter a range of items, and a date range as
well. Currently my query returns all items in the specified range whether or
not I sold any of the items. I would like to add a check box on my form that
would allow me to exclude items that do not sell during the given date
ranges. When I put the following in as the criteria for my query nothing is
returned.

IIf([Forms]![F Inventory and Date Range]![ZeroSales]=True,Not 0)

Any help is much appreciated.

Here is the sql for the entire query

SELECT P02INMAS.IN_STOCK, P02INMAS.IN_DES, Sum([Q Sort History].PSHDQTY) AS
Total
FROM P02INMAS LEFT JOIN [Q Sort History] ON P02INMAS.IN_STOCK = [Q Sort
History].PSHDSTK
GROUP BY P02INMAS.IN_STOCK, P02INMAS.IN_DES
HAVING (((P02INMAS.IN_STOCK) Between [Forms]![F Inventory and Date
Range]![BeginningStock] And [Forms]![F Inventory and Date
Range]![EndingStock]) AND ((Sum([Q Sort History].PSHDQTY))=IIf([Forms]![F
Inventory and Date Range]![ZeroSales]=True,Not (Sum([Q Sort
History].PSHDQTY))=0)));
 
Perhaps loop through the checkboxes, such that you can construct a NOT IN()
clause.

Alternatively, loop through such that you create an IN() clause for the ones
that you want.
 
Assuming that you want the zero sales totals if the checkbox is checked,
PERHAPS the following will work for you.

SELECT P02INMAS.IN_STOCK
, P02INMAS.IN_DES
, Sum([Q Sort History].PSHDQTY) AS Total
FROM P02INMAS LEFT JOIN [Q Sort History]
ON P02INMAS.IN_STOCK = [Q Sort History].PSHDSTK
WHERE P02INMAS.IN_STOCK Between
[Forms]![F Inventory and Date Range]![BeginningStock]
And [Forms]![F Inventory and Date Range]![EndingStock])
GROUP BY P02INMAS.IN_STOCK, P02INMAS.IN_DES
HAVING Sum([Q Sort History].PSHDQTY) <> 0
OR [Forms]![F Inventory and Date Range]![ZeroSales]=True

If that fails try
HAVING Sum([Q Sort History].PSHDQTY) >
IIf([Forms]![F Inventory and Date Range]![ZeroSales]=True,-1,0)




John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top