Hi,
I want to return sales figures for all companies or companies outside of the UK based on the value of a checkbox in a form.
I cannot get the IIF statement in the criteria row of the query to work…
This is the code string I entered…
So if the checkbox is ticked, the criteria should be <> “United Kingdom”
To test which part of the code string was causing the problem I entered this…
Which works (ie it returns UK orders if ticked and USA orders if not) so the problem seems to be with the <> part.
Incedentally, when I save the query and go back into it, Access has changed the code string to this…
I can see the sense of this but it still doesn’t work.
I could create a hidden text box in the form to hold the value "<>United Kingdom" and just set the criteria in the query to point there but I would like to do it with the <> within the Iif statement.
For your reference, here is the entire SQL code for the query...
Any help would be much appreciated.
Neil
I want to return sales figures for all companies or companies outside of the UK based on the value of a checkbox in a form.
I cannot get the IIF statement in the criteria row of the query to work…
This is the code string I entered…
Code:
IIf([Forms]![fCustOrderMarginReport]![chkExportOnly]=True,<>"United Kingdom",<>"")
So if the checkbox is ticked, the criteria should be <> “United Kingdom”
To test which part of the code string was causing the problem I entered this…
Code:
IIf([Forms]![fCustOrderMarginReport]![chkExportOnly]=True,"United Kingdom","USA")
Incedentally, when I save the query and go back into it, Access has changed the code string to this…
Code:
IIf([Forms]![fCustOrderMarginReport]![chkExportOnly]=True,([tCustomer].[Country])<>"United Kingdom",([tCustomer].[Country])<>"")
I can see the sense of this but it still doesn’t work.
I could create a hidden text box in the form to hold the value "<>United Kingdom" and just set the criteria in the query to point there but I would like to do it with the <> within the Iif statement.
For your reference, here is the entire SQL code for the query...
Code:
SELECT tCustOrder.CustomerID, tCustomer.Company, Sum(nz([qCOECostAndSale].[Qty],0)*nz([qCOECostAndSale].[UnitCost],0)) AS TotCost, Sum(nz([qCOECostAndSale].[Qty],0)*nz([qCOECostAndSale].[SellPrice],0)) AS TotSale, IIf([TotSale]=0,0,(([TotSale]-[TotCost])/[TotSale])) AS Margin, Count(tCustOrder.CustOrderID) AS CountOfCustOrderID
FROM (tCustomer INNER JOIN tCustOrder ON tCustomer.CustomerID = tCustOrder.CustomerID) INNER JOIN (qCOECostAndSale INNER JOIN tCustOrderEntry ON qCOECostAndSale.CustOrderEntryID = tCustOrderEntry.CustOrderEntryID) ON tCustOrder.CustOrderID = tCustOrderEntry.CustOrderID
WHERE (((tCustOrderEntry.DeliveryDate) Between getDate("fCustOrderMarginReport","txtDateFrom","Start") And getDate("fCustOrderMarginReport","txtDateTo","End")) AND ((tCustomer.Country)=IIf([Forms]![fCustOrderMarginReport]![chkExportOnly]=True,([tCustomer].[Country])<>"United Kingdom",([tCustomer].[Country])<>"")))
GROUP BY tCustOrder.CustomerID, tCustomer.Company;
Any help would be much appreciated.
Neil