Using IIF in criteria of query 1

[NSNewey]

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…

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…

IIf([Forms]![fCustOrderMarginReport]![chkExportOnly]=True,"United Kingdom","USA")

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…

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...

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

 

[SkipVought]

Having a hard time following you but...

IIf([Forms]![fCustOrderMarginReport]![chkExportOnly],"","United Kingdom")

So...
[tt]
IF [Your Expression Is TRUE] Then return "" Else return "UnitedKingdom"
[/tt]
YES?




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NSNewey]

Hi Skip,

No...

If the expression is true I want to return all non UK orders else all orders.

In a standard query which is not referring to a control on a form I would just enter <> "United Kingdom" and it would return all orders not for UK.

Cheers,

Neil

 

[PHV]

In the criteria cell:
<>IIf([Forms]![fCustOrderMarginReport]![chkExportOnly]=True,"United Kingdom","")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NSNewey]

Thanks PHV

That works a treat. You're a genius!!!

Neil