Query Field Formula

[Guest]

Thanks for taking the time to read my question.

In the Field line of my query I need to have a formula.

Ex: iif(x=1,iif(x=3,iif(x=6,iif(x=8,iif(x=22,1*2,0),0),0),0),0)

Is there a better way of doing this? In my real formula this is going to be
a mile long, and if any of the criteria ever change, I'm toast.

Thanks,

Brad

 

[Duane Hookom]

Have you considered modeling this in data? For instance create a table of X
values with their associated other value? If this was an expression to
calculate grade point averages
tblLetterPoint
A 4
B 3
C 2
D 1
F 0

Then rather than using a complex IIf() or Switch(), you can add
tblLetterPoint to your query and join the "letter" fields. Use the "point"
field in your query rather than a complex expression.