Hello, and Thanks in advance for your help.

I'm trying to create an iif statment in a query using "AND". I have one set of iifs that works. I need to add about 10 more other iifs with a change in "And". When I add the 10 more or so iifs I'm told I need to truncate my statement. Is there a way I can use "OR" for when the calculations are the same,or does anyone have any suggestions? I did try using "OR" but when I ran a test with only two [PayCode] variables I found that instances of other variables were being calculated as well.

One thought has been to create multiple queries, but ideally I'd like to do all my calculations for this field all at once.

Pay: IIf([PayCode]="HRLY" And [Pay Type]="H",[Hours]*[Base Pay],IIf([PayCode]="HRLY2" And [Pay Type]="H",[Hours]*([Base Pay]+0.2),IIf([PayCode]="OT" And [Pay Type]="H",[Hours]*1.5*[Base Pay],IIf([PayCode]="OT2" And [Pay Type]="H",[Hours]*1.5*([Base Pay]+0.2),IIf([PayCode]="%SCK1" And [Pay Type]="H",[Hours]*[Base Pay],IIf([PayCode]="%SCK2" And [Pay Type]="H",[Hours]*[Base Pay],IIf([PayCode]="%VAC1" And [Pay Type]="H",[Hours]*[Base Pay],IIf([PayCode]="%VAC2" And [Pay Type]="H",[Hours]*[Base Pay],IIf([PayCode]="%FNL1" And [Pay Type]="H",[Hours]*[Base Pay],IIf([PayCode]="%FNL2" And [Pay Type]="H",[Hours]*[Base Pay],IIf([PayCode]="%HOL1" And [Pay Type]="H",[Hours]*[Base Pay],IIf([PayCode]="%HOL2" And [Pay Type]="H",[Hours]*[Base Pay]))))))))))))

Thus far, the example shows 12 pay codes, but with only two sets of exceptions
the pay rate = [Hours] * [Base Pay].

There's no need to repeat [Hours] * [ Base Pay] for each set of circumstances. Instead,
just solve for the exceptions, e.g.

Pay = iif([Pay Type] = "H", ([Hours]* [Base Pay]* Iif(Left([PayCode],2) = "OT" , 1.5, 1) + Iif(instr("HRLY2 OT2", [PayCode])>0, 0.2, 0), show formula if [Pay Type]<>"H")