IIf statement in query criteria

[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)));

 

[S.Clark]

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.

 

[John Spencer]

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

 

[Access WannaBe]

Thank you for your help. I found another post that solved my problem. I
look for an entire day yesterday before I found this one this after noon.

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us