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?
IIF in Query
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
IIF in Query
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IIF in Query
You could use nested IIfs or alternatively the Switch function, but it quickly becomes unmanageable.
Instead, I would create a lookup table:
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.
Instead, I would create a lookup table:
Input | Output |
CUSTOMER JOB | Retail |
NICE JOB | Sales |
NOSE JOB | Plastic surgery |
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: IIF in Query
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
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
Last edited by D Willett on 11 Nov 2010, 18:07, edited 1 time in total.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IIF in Query
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.
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: IIF in Query
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
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
Cheers ...
Dave.
Dave.
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: IIF in Query
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 ?
Just added the or statement to handle Null or 0 length, it doesn't like it ?
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IIF in Query
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")
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")
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England