iif statement

[jligue]

I am new to access; trying to create a query for a report that has a subtotal,
the field will only total if a portion of a word appears in a combo box of
another field. Specifically, if the Product Type field (which is a combo box)
has the word "bag" in it, I want the value in the Qty field to be counted.
If the word "bag" is not in the Product Type field, then no value is
populated in the newly created field. Ultimately a final subtotal will be
needed for the report.

 

[John Spencer]

You would need an expression something like the following as a control's
control source
=SUM(IIF(" " & [Product Type] & " " Like "* BAG *",Qty,Null))

or you could use a calculated field in the query and then sum it in the report.

Field: BAGQty: IIF(" " & [Product Type] & " " Like "* BAG *",Qty,Null)


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