Using IIf in Query Criteria & multiple table expression

Abiart

Registered User.
Local time
Today, 09:10
Joined
May 17, 2006
Messages
27
Hi everyone,

My query is coming along nicely, but as always once one problem is solved you find another :rolleyes: !

My problem is that I have thus far specified criteria for the field OrdDeliveryCountry, but this field is not filled in unless the delivery address is different from the default address for the customer, therefore it is frequently blank and so the query wasn't finding all records, only those where the Delivery Address was specific to the order.

I want to use the IIf function to make an expression to say (in linguistic terms):
If OrdDeliveryCountry is blank, then use the country in the Customers table.

Sounds simple enough, but the criteria currently is:
Code:
WHERE    (((ORDERS.ORDDELIVERYCOUNTRY) = "Austria"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
            OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden")
          AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade"
               AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair"
               AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr"
               AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission")
          AND ((ORDERS.[DEMO/SALEID]) = 2))


So how do I combine the IIf(expr,truepart,falsepart) with "Is Not x Or x Or x"?I.E. I need to get it to exclude records where OrdDeliveryCountry does not equal one in the list, and if that is blank then the Country field in the Customers table does not equal one in the list?

My attempt is this, but I think I'm way off the mark

Code:
SELECT   ORDERS.SHIPDATE,
         PRODUCTS.[STANDARD TARRIFF NUMBER],
         [ORDER DETAILS].[QUANTITY] * [ORDER DETAILS].[UNITPRICE] * (1 - [DISCOUNT]) * (1 - [SPECIAL DISCOUNT]) AS LINETOTAL,
         [ORDER DETAILS].QUANTITY,
         ORDERS.ORDDELIVERYCOUNTRY,
         ORDERS.ORDERID,
         [ORDER DETAILS].PRODUCTID
FROM     CUSTOMERS
         RIGHT JOIN (PRODUCTS
                     RIGHT JOIN (ORDERS
                                 LEFT JOIN [ORDER DETAILS]
                                   ON ORDERS.ORDERID = [ORDER DETAILS].ORDERID)
                       ON PRODUCTS.PRODUCTID = [ORDER DETAILS].PRODUCTID)
           ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID
WHERE    (((ORDERS.ORDDELIVERYCOUNTRY) = IIF(ISNULL([ORDERS]![ORDDELIVERYCOUNTRY]),([CUSTOMERS]![COUNTRY] NOT LIKE "Austria"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
                                                                                     OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden"),
                                             (([ORDERS]![ORDDELIVERYCOUNTRY]) NOT LIKE "Austria"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
                                               OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden")))
          AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade"
               AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair"
               AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr"
               AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission")
          AND ((ORDERS.[DEMO/SALEID]) = 2))
ORDER BY ORDERS.SHIPDATE DESC;

My thoughts:
  • Maybe I need to re-structure the WHERE clause?
  • Would it work if the IIf expresssion was in the SELECT part not the WHERE part?

I would really appreciate some help with this: I'm not sufficiently familiar with structuring statements as complex as this and I don't know all the syntax rules etc.

Thanks in advance!!
 
I think you build your query in the QBE grid, the simple way to do what you want is to make a field in the query where your Iff statement goes and move the selection criterea to that calqulated field.

The expression for the field name would be someting like

qrySelectCountry: IIf(isnull([ORDDELIVERYCOUNTRY]);[Customers]![country] ;[ORDDELIVERYCOUNTRY])
 
You're a lifesaver! Thanks, I wondered if some of the IIf expression needed to go elsewhere, I just didn't know how to go about it. I used the QBE grid as you suggested (I've been in and out of it all along, but posting in SQL as it's the easiest way of showing what I've got).

Thanks also for the SQL code formatter link in the last thread, very handy!
 

Users who are viewing this thread

Back
Top Bottom