IIF in Query

[D Willett]

The following iif within my query produces results of "None Insurer" if the field is Null or length of 0, straight forward and works fine.

Client: IIf(Len(Trim([INS_NME]))=0 Or IsNull([INS_NME]),"None Insurer",[INS_NME])

I am being asked to expand on this ie the following examples:

If INS_NME = Null or length 0 the result should be "None Insurer" ( we already have this )
If INS_NME = "CUSTOMER JOB" the result should be "Retail"
if INS_NME = etc

The field can hold many values so the question is is it possible to extend the parameter to accomodate this and output many results instead of the one as the above?

[HansV]

You could use nested IIfs or alternatively the Switch function, but it quickly becomes unmanageable.
Instead, I would create a lookup table:

Input	Output
CUSTOMER JOB	Retail
NICE JOB	Sales
NOSE JOB	Plastic surgery

The Input field is the primary key. Add the table to the query, join it to INS_NME on the Input field, then add the Output field to the query grid.

[D Willett]

Hi Hans

The table idea looks good but not sure if it will suit the purpose as the INS_NME field holds many many different names.
On the other hand, I can perhaps do this another way based on a field which just holds a single character.
Field [ENQ_SRC] holds 11 different codes which determine the job type as follows:

If the field contains A or B or I or J or M or T then the query should output "Insurer"
If the field contains C the query should output "Internal"
If the field contains D or P or S the query should output "Retail"
If the field contains W the query should output "Total Loss"

Can this be contained in a nested iif statement and if so how?

The following returns "Insurer":

JobType: IIf([ENQ_SRC] In ("A","B","I","J","M","T"),"Insurer",([ENQ_SRC]))

But how to include the other letters and output is beyond me.

Thanks

[HansV]

You could use

JobType: IIf([ENQ_SRC] In ("A","B","I","J","M","T"),"Insurer",IIf([ENQ_SRC]="C","Internal",IIf([ENQ_SRC] In ("D","P","S"),"Retail",IIf([ENQ_SRC]="W","Total Loss",""))))

or

JobType: Switch([ENQ_SRC] In ("A","B","I","J","M","T"),"Insurer",[ENQ_SRC]="C","Internal",[ENQ_SRC] In ("D","P","S"),"Retail",[ENQ_SRC]="W","Total Loss")

A table would still be more flexible - if you want to add, change or remove codes, you only have to edit the table instead of the rather convoluted expressions.

[D Willett]

JobType: Switch([ENQ_SRC] In ("A","B","I","J","M","T"),"Insurer",[ENQ_SRC]="C","Internal",[ENQ_SRC] In ("D","P","S"),"Retail",[ENQ_SRC]="W","Total Loss")

This was the one I was looking for.. thanks again Hans, this has saved me a real headache with one of our manufacturer approvals.

Thanks

[D Willett]

JobType: Switch([ENQ_SRC] In ("A","B","I","J","M","T"),"Insurer",[ENQ_SRC]="C","Internal",[ENQ_SRC] In ("D","P","S"),"Retail",[ENQ_SRC]="W","Total Loss" Or IIf(Len(Trim([ENQ_SRC]))=0 Or IsNull([ENQ_SRC]),"Misc"))

Just added the or statement to handle Null or 0 length, it doesn't like it ?

[HansV]

Switch expects a series of comma-separated pairs condition, value_if_true.

Change it to

JobType: Switch([ENQ_SRC] In ("A","B","I","J","M","T"),"Insurer",[ENQ_SRC]="C","Internal",[ENQ_SRC] In ("D","P","S"),"Retail",[ENQ_SRC]="W","Total Loss",Trim([ENQ_SRC] & "")="","Misc")

[D Willett]

Cheers Hans, works great.

Regards