Nested IIF Function

[JohnLee]

I have a query that has a product code field and an Agent Number field.

The Product code field data looks like this XXAW24, XXUT12 etc.

The Agent Number filed data looks like this 29221 and 67322

The product code filed is called "strProductCode" and the agent number field is called "strAgentNo"

Product codes that end in 24 are always associated with agent number 67322 and product codes that end in 12 are always associated with agent number 29221.

In my query I have written the follow IIF function:

AgentNo: IIf([strAgentNo]="67322",IIf(Right([strProductCode],2)="12","29221",IIf([strAgentNo]="29221",IIf(Right([strProductCode],2)="24","67322",[strAgentNo]))))

Now it corrects the records where the agent number is 67322 were the right two characters of the product code field are 12, but it doesn correct the records where the agent number is 29221 and the right two characters of the product code field are 24 and where records do not meet any of these criteria, it doesn't retain the agent number at all, the field is blank in these cases.

Clearly I must be doing something wrong, but I can't seem to work out where that is, some assistance would be appreciated.

John

 

[Extra_Cover]

Try using AND instead of IIF ;

AgentNo: IIf([strAgentNo]="67322" AND Right([strProductCode],2)="12","29221",IIf([strAgentNo]="29221" AND Right([strProductCode],2)="24","67322",[strAgentNo]))

 

[namliam]

Try re-writing this to something readable...

AgentNo: 
IIf([strAgentNo]="67322"
 ,IIf(Right([strProductCode],2)="12"
  ,"29221"
  ,IIf([strAgentNo]="29221"
   ,IIf(Right([strProductCode],2)="24"
    ,"67322"
    ,[strAgentNo]))))

Doing this shows me that your else of the first IIF not not beeing used at all.... use that else and your good to go....

 

[raskew]

Hi -

Nested Iif() statements can be a problem. One misplaced parenthesis and you're sunk. You might consider the Switch() function, e.g.

AgentNo: Switch([strAgentNo]="67322" and Right([strProductCode],2)="12", "29221", [strAgentNo]="29221" and Right([strProductCode],2)="24", "67322", True, [strAgentNo])

HTH - Bob

 

[Atomic Shrimp]

Hi -

Nested Iif() statements can be a problem. One misplaced parenthesis and you're sunk.

Too true. Whenever I do them now, I create a template first (in Notepad) with placeholder values, then paste in the real variable or field names once I've built the framework.

If the OP's query is going to be used more than once in the future, a better approach would be to create a lookup table relating the digits to the agent number - making it easy to cope with an organisational restructure or change of staff.

 

[namliam]

Too true.

NOT TRUE

The problem with nested -anything- is keeping track of things.

You do this by KEEPING THINGS READABLE, either space like I do, use a template like you do... What ever you need to do to keep it READABLE.

READABLE = doable

 

[JohnLee]

Thanks everyone,

Wow so many replies all most useful and helpful, I've now been able to put right what I got wrong, thanks very much.

For the product code aspect I have created a lookup table, because there are so many, and that query runs before this one and works as required. For this query as there will only ever be the two agent numbers I have followed Extra_Covers example, which has saved me having to create further queries to address the problem.

Thanks once again folks, great help indeed.

John