Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using IIF in criteria of query 1

Status
Not open for further replies.

NSNewey

Programmer
Jun 29, 2005
125
GB
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…

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")
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…

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
 



Having a hard time following you but...
Code:
IIf([Forms]![fCustOrderMarginReport]![chkExportOnly],"","United Kingdom")
So...
[tt]
IF [Your Expression Is TRUE] Then return "" Else return "UnitedKingdom"
[/tt]
YES?




Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 
In the criteria cell:
<>IIf([Forms]![fCustOrderMarginReport]![chkExportOnly]=True,"United Kingdom","")

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

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

Neil
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top