Newbie: Why is my IIf Statement not working

[mbtedrick]

Hello All, I am creating a query with several fields that are calculated off of other fields in a table. I currently have 4 set up in the same query, two are working and two are not. These IIf statments are basically looking at other fields to see if conditions apply then inputting a response in the calculated field. I cannot find what I am doing wrong. I have tried to simplify them and still nothing. If you have some sympathy for someone who barely knows enough to get by, please review the Not Working items below and see what you think :

Working:
1. Job: IIf([Summary - Master].[Job Date Opened]>#1/1/2000#,"1","")
2. Closed: IIf([Summary - Master].[Job Process Control]="Closed","1",IIf([Summary - Master].[Job Process Control]="A/P, I/O and Billing","1",IIf([Summary - Master].[Job Process Control]="No Processing","1",IIf([Summary - Master].[Job Process Control]="I/O and Billing","1",IIf([Summary - Master].[Job Process Control]="Billing Only","1","")))))

Not Working:
3. Segment: IIf([Summary - Master].[Campaign Name]='2013*',"2013 Playbook",IIf([Summary - Master].[Campaign Name]="2014*","2014 Playbook",IIf([Summary - Master].[Campaign Name]="2015*","2015 Playbook",IIf([Summary - Master].[Campaign Name]="2016*","2016 Playbook",IIf([Summary - Master].[Campaign Name]="2017*","2017 Playbook",IIf([Summary - Master].[Campaign Name]="2018*","2018 Playbook",IIf([Summary - Master].[Campaign Name]="2019*","2019 Playbook","")))))))

4. INC: IIf([Job Component]="*_INC","1",IIf([Component Description]="*_INC","1",""))

 

[xenou]

equals doesn't work with * expressions. You need LIKE for that.

 

[xenou]

Possibly rewrite this way:

Job: IIf([Summary - Master].[Job Date Opened]>#1/1/2000#,"1","")

Closed: IIf([Summary - Master].[Job Process Control] IN ("Closed","A/P, I/O and Billing","No Processing","I/O and Billing","Billing Only"),"1","")

Segment: IIF(Left([Summary - Master].[Campaign Name], 4) in ("2013","2014","2015","2016","2017","2018"), Left([Summary - Master].[Campaign Name], 4) + " Playbook", "")

INC: IIF([Job Component] LIKE "*_INC" OR [Component Description] LIKE "*_INC", "1", "")

 

[mbtedrick]

Xenou, Thank you so much for your quick response. They work beautifully!!

After posting my original question, I realized there was other criteria I needed to check for under the Segment: I am not sure if this is even feasible as I am checking another field completely. and how it would change your original suggestion

Segment: IIf([Summary - Master].[Campaign Name]='2013*',"2013 Playbook",IIf([Summary - Master].[Campaign Name]="2014*","2014 Playbook",IIf([Summary - Master].[Campaign Name]="2015*","2015 Playbook",IIf([Summary - Master].[Campaign Name]="2016*","2016 Playbook",IIf([Summary - Master].[Campaign Name]="2017*","2017 Playbook",IIf([Summary - Master].[Campaign Name]="2018*","2018 Playbook",IIf([Summary - Master].[INC]="1","Incremental",IIf([Summary - Master].[Product Code]="oosj","Out of Scope""")))))))

Thank you so much for your help.

Ellen

 

[xenou]

I think you can just drop that on the end:

Segment: IIF(Left([Summary - Master].[Campaign Name], 4) in ("2013","2014","2015","2016","2017","2018"), Left([Summary - Master].[Campaign Name], 4) + " Playbook",IIf([Summary - Master].[INC]="1","Incremental",IIf([Summary - Master].[Product Code]="oosj","Out of Scope","")))

For the record I rarely use IIF and never use nested IIFs so in general this isn't optimal but it will probably work. It's very hard to read and maintain sql that has these longwinded formulas in them.

 

[mbtedrick]

I am just learning and do not have any sql experience. If you have any suggestions on good reference tools (books, classes etc) it would be greatly appreciated. Use the internet when looking for specific things but feel I need a class to get a better handle on the big picture, but am just not sure where to start.

Thank you again for your help.

Ellen

 

[xenou]

I have some recommendations posted here:
https://www.mrexcel.com/forum/microsoft-access/756058-best-way-learn-access.html

and also see here:
https://www.mrexcel.com/forum/microsoft-access/764238-best-access-book-beginner.html