Complex Nested If Statement

[qrk10000]

Hello,

I have been working on a query for a few hours now to create a new field based on data in other fields in my table. Here is the code I am using:

MBkt: IIf([Table14].[OWNERGRP_1]<>"",IIf([Table14].[OWNERGRP_1]="ADVO",IIf([Table14].[PRICINGMKT]="SHAREDEX","SEZ"),IIf([Table14].[PRICINGMKT]="REP","REP"),IIf([Table14].[PRICINGMKT]="RIP","REP"),IIf([Table14].[COV_FREQ]="Weekly PCD","PCD"),IIf([Table14].[COV_FREQ]="Weekly","ADVO WKLY","ADVO MULTI-WEEKS"),IIf([Table14].[OWNERGRP_1]="ANNE",IIf([Table14].[OWNER_1]="MMSI/MAIL MARKETING/ANNE","ANNE MMSI","ANNE"),"SOLO"))

Unfortunately, I am unable to get this to work. Any help that anyone can provide is greatly appreciated.

Thanks,

-Brian

 

[Moniker]

Holy ***...

There is a limit to the number of nested IIf statements (although I can't think of it off the top of my head). EDIT -- There is a limit of seven nested IF statments. However, there are a ton of issues with what you've done alwady, including non-matched parentheses, omitting values, etc.

The much cleaner and easier-to-read-and-maintain way to do that is to use a SWITCH() Function instead, which I have outlined here.

 

[Moniker]

Even the SWITCH Function for that is unruly and tough to decipher. Note that I'm not sure this would even work on the first try:

MBkt:
SWITCH([Table14].[OWNERGRP_1]<>"" 
    AND [Table14].[OWNERGRP_1]="ADVO" 
    AND (SWITCH([Table14].[PRICINGMKT]="SHAREDEX',"SEZ"
        ,([Table14].[PRICINGMKT]="REP"
        OR [Table14].[PRICINGMKT]="RIP","REP")))
    AND (SWITCH([Table14].[COV_FREQ]="Weekly PCD","PCD"
        ,[Table14].[COV_FREQ]="Weekly", "ADVO WKLY"
        ,TRUE,"ADVO MULTI-WEEKS"))
    AND ([Table14].[OWNERGRP_1]="ANNE" 
        AND [Table14].[OWNER_1]="MMSI/MAIL MARKETING/ANNE")
    , "ANNE MMSI"
    , ([Table14].[OWNERGRP_1]="ANNE" 
        AND [Table14].[OWNER_1]<>"MMSI/MAIL MARKETING/ANNE")
    , "ANNE"
    , TRUE
    , "SOLO")

A much cleaner route is going to be to do your transformations (IF This THEN That statements) separately and then use the results from that.

 

[qrk10000]

Thanks for your help. I am confused as to how I go about setting this up, though. Essentially I need to come out with 7 values in this field. Will the SWITCH function allow me to do this or will it only output one true value and one false value?

 

[qrk10000]

Sorry, didn't see your reply. Thanks for all your help. Can you recommend a book or something that I can reference when working with advanced queries? I have had to learn this on the fly and it has been tough, to say the least.

 

[KeithG]

I would create a custome VBA function to determine the value.

 

[Moniker]

I can't think of any books off the top of my head, although I've been happy with the stuff by Wrox before. (Google it if interested.)

Keep in mind that what you're trying to do is not that complicated. You've made it overly complicated by trying to do everything in one step. It would be much easier to read and maintain if it was all in separte steps, something like this:

TmpPricingMkt: 
=SWITCH([PRICINGMKT]="SHAREDEX',"SEZ"
    ,[PRICINGMKT]="REP","REP"
    ,[PRICINGMKT]="RIP","REP"
    ,TRUE,"Other Pricing Mkt")

TmpCoverFreq: 
=SWITCH([COV_FREQ]="Weekly PCD","PCD"
        ,[COV_FREQ]="Weekly", "ADVO WKLY"
        ,TRUE,"ADVO MULTI-WEEKS")

TmpPersonsName:
=SWITCH([OWNERGRP_1]="ANNE" 
        AND [OWNER_1]="MMSI/MAIL MARKETING/ANNE"
    ,"ANNE MMSI"
    ,[OWNERGRP_1]="ANNE" 
        AND [OWNER_1]<>"MMSI/MAIL MARKETING/ANNE"
    ,"ANNE"
    ,TRUE,"SOLO")

MBkt = TmpPricingMkt & TmpCoverFreq & TmpPersonsName

EDIT: Fixed the sample code on how to potentially separate things out and make it easier to read and maintain.