Nested IIF Statement produces 0's and -1's??

[djmousie]

I'm trying to write an update query that has nested IF's, however, when I run the update I get 0's and -1's instead of the values Im asking, my query logic is below:

IIf([LEAD]="CHRISTINE",1) Or IIf([LEAD]="KEVIN",2) Or IIf([LEAD]="LYNNE",3) Or IIf([LEAD]="TANYA",4) Or IIf([LEAD]="JACOB",5) Or IIf([LEAD]="GERALDINE",6) Or IIf([LEAD]="JAIME",7) Or IIf([LEAD]="MARICA",8) Or IIf([LEAD]="MELINDA",9) Or IIf([LEAD]="JAIME/JAKE",10) Or IIf([LEAD]="LYNNE/MELINDA",11)

I have two (2) fields, one labeled LEAD and the other labeled LEAD NUMBER. Wherever the LEAD name appears I want the LEAD NUMBER field to update with the corresponding number IDs for each.

 

[SteveR77]

The evaluation of an IIF statement will give you 0 for false and a -1 for true.

The issue in your case is the syntax of the IIF statement for nesting is incorrect you do not need the OR statement.

IIf([LEAD]="CHRISTINE",1,IIf([LEAD]="KEVIN",2,IIf([LEAD]="LYNNE",3,IIf([LEAD]="TANYA",4,IIf([LEAD]="JACOB",5,IIf([LEAD]="GERALDINE",6,IIf([LEAD]="JAIME",7,IIf([LEAD]="MARICA",8,IIf([LEAD]="MELINDA",9,IIf([LEAD]="JAIME/JAKE",10,IIf([LEAD]="LYNNE/MELINDA",11,[LEAD])))))))))))

If I have the placement of the parens correct this should be what you need.

HTH,

Steve

 

[Golom]

Your syntax is not quite correct.

IIf([LEAD]="CHRISTINE",1,
IIf([LEAD]="KEVIN",2,
IIf([LEAD]="LYNNE",3,
IIf([LEAD]="TANYA",4,
IIf([LEAD]="JACOB",5,
IIf([LEAD]="GERALDINE",6,
IIf([LEAD]="JAIME",7,
IIf([LEAD]="MARICA",8,
IIf([LEAD]="MELINDA",9,
IIf([LEAD]="JAIME/JAKE",10,
IIf([LEAD]="LYNNE/MELINDA",11,99)))))))))))

I've introduced "99" as the value when [Lead] doesn't match any of the names.

 

[djmousie]

Thanks guys, I see where i went wrong.

Thanks!

 

[dhookom]

djousie,
I think where you went wrong is to create a huge expression where you should be using a lookup table. You should be maintaining data, not hard-coded expressions. You should never have to change anything other than data records if you add, remove, or modify leads.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.