iif for 16 conditions

[harpreett.singhh@gmail.co]

Hi,

I have 16 IIF conditions to put into a table where the value of the cell depends on the other value.

If I select any value out of 16 values of STEP # column then the FINAL STATUS should change to particular value.

I do not know how to use number range in this, is it possible to give a range ? instead working on individual numbers ? this way I will reduce the text to avoid access saying me too complex query (means it accepts only 14 situations).

IIf([Step #]=1,"Open",IIf([Step #]=2,"Construction Complete",IIf([Step #]=3,"Documentation Colladed",IIf([Step #]=4,"Final Review",IIf([Step #]=5,"Final Review",IIf([Step #]=6,"Final Review",IIf([Step #]=7,"Final Sign Off",IIf([Step #]=8,"Final Sign off",IIf([Step #]=9,"Final Sign off",IIf([Step #]=10,"Final Sign off",IIf([Step #]=11,"Final Sign off",IIf([Step #]=12,"Final Sign off",IIf([Step #]=13,"Hibernation",IIf([Step #]=14,"Snaglist","Nill"))))))))))))))



Please advise.

Regards

[June7]

Certainly. Try:

IIf([Step #]=1,"Open",IIf([Step #]=2,"Construction Complete",IIf([Step #]=3,"Documentation Collated",IIf([Step #]<7, "Final Review", IIf([Step #]<13,"Final Sign Off", IIf([Step #]=13,"Hibernation",IIf([Step #]=14,"Snaglist","Nill")))))))

Also, explore the Choose and Switch functions.

Otherwise, build a table of the Step numbers and descriptions and include that table in queries.


BTW, advise no spaces or special characters/punctuation in naming convention. Better would be StepNum, StepNo, Step_Num.

[RuralGuy]

I prefer Public Functions in Standard Modules for such operations. Easier to understand and document and fix years later. I would probably use a Select Case structure.

[harpreett.singhh@gmail.co]

Thanks mate. its solved. I have provided range for the steps.