Untangle a mess

[Jeff C]

I have looked until cross-eyed at this and cannot find the problem:

iif([tbl_NewPayroll]![Code1]=â€01â€,[tbl_New_Payroll]![Hours1],iif([tbl_NewPayroll]![Code2]=â€01â€,[tbl_New_Payroll]![Hours2],iif([tbl_NewPayroll]![Code3]=â€01â€,[tbl_New_Payroll]![Hours3],iif([tbl_NewPayroll]![Code4]=â€01â€,[tbl_New_Payroll]![Hours4],iif([tbl_NewPayroll]![Code5]=â€01â€,[tbl_New_Payroll]![Hours5],iif([tbl_NewPayroll]![Code6]=â€01â€,[tbl_New_Payroll]![Hours6],iif([tbl_NewPayroll]![Code7]=â€01â€,[tbl_New_Payroll]![Hours7],iif([tbl_NewPayroll]![Code8]=â€01â€,[tbl_New_Payroll]![Hours8],iif([tbl_NewPayroll]![Code9]=â€01â€,[tbl_New_Payroll]![Hours9],[tbl_NewPayroll]![Code10]=â€01â€,[tbl_New_Payroll]![Hours10])))))))))

I also tried this with no luck:

Switch([tbl_NewPayroll]![Code1]="â€01â€",[tbl_New_Payroll]![Hours1],[tbl_NewPayroll]![Code2]="â€01â€",[tbl_New_Payroll]![Hours2],[tbl_NewPayroll]![Code3]="â€01â€",[tbl_New_Payroll]![Hours3],[tbl_NewPayroll]![Code4]="â€01â€",[tbl_New_Payroll]![Hours4],[tbl_NewPayroll]![Code6]="â€01â€",[tbl_New_Payroll]![Hours6],[tbl_NewPayroll]![Code7]="â€01â€",[tbl_New_Payroll]![Hours7],[tbl_NewPayroll]![Code8]="â€01â€",[tbl_New_Payroll]![Hours8],[tbl_NewPayroll]![Code9]="â€01â€",[tbl_New_Payroll]![Hours9],[tbl_NewPayroll]![Code10]="â€01â€",[tbl_New_Payroll]![Hours10])

I am using this in an update query, the second attempt gets me a parameter
message looking for the "True" field. Anyone have an idea? Thank you

 

[xRoachx]

My suggestion: Use a Select Case Statement or a loop rather then this many
nested IIf's. But looking at your first mess of IIf statements, you don't
have an IIf for Code10="01" and a final statement of what happens if it is
none of these--I added a 0 for this and another paranthesis:

iif([tbl_NewPayroll]![Code1]=â€01â€,[tbl_New_Payroll]![Hours1],iif
([tbl_NewPayroll]![Code2]=â€01â€,[tbl_New_Payroll]![Hours2],iif
([tbl_NewPayroll]![Code3]=â€01â€,[tbl_New_Payroll]![Hours3],iif
([tbl_NewPayroll]![Code4]=â€01â€,[tbl_New_Payroll]![Hours4],iif
([tbl_NewPayroll]![Code5]=â€01â€,[tbl_New_Payroll]![Hours5],iif
([tbl_NewPayroll]![Code6]=â€01â€,[tbl_New_Payroll]![Hours6],iif
([tbl_NewPayroll]![Code7]=â€01â€,[tbl_New_Payroll]![Hours7],iif
([tbl_NewPayroll]![Code8]=â€01â€,[tbl_New_Payroll]![Hours8],iif
([tbl_NewPayroll]![Code9]=â€01â€,[tbl_New_Payroll]![Hours9],iif
([tbl_NewPayroll]![Code10]=â€01â€,[tbl_New_Payroll]![Hours10],0))))))))))