Switch Statement and IIF statements are too long. What is my other option

[ItsMe]

What would the MyPlan#Function look like?

I would probably develop a formula using values from a table that would indicate upper threshold, lower threshold, percentage, etc.

Develop your formula using variables. You can hard code the values of your variables while you develop your code. Later, you can place the values in a table, where your variables can reference them. Create the table after testing that your function works.

If you use a case select to call different functions, those functions will probably have another Case Select statement and or an If Then Else. The tutorials I linked to happen to go over Functions, If Then Else Statements, and Select Case Statements. It will take a while to go through the Introduction to VBA series of tutorials. But, I believe they are relevant to what you are doing.

[ahoneycutt]

I think you better go with a table and domain aggregate.

Thanks June7 but can you explain a little more. I have the qry and obviously I can put it in a table. All the fields except for the one I am trying to calculate is already in a table. I am pulling the data from a table which is currently in rows by person but I have to evaluate the EB and Direct Control and many other fields which are now columns in my crosstab. I can dump this into a table but then what? I am still evaluating up to 3 columns of information for each person at the minimum.

Thanks again. Sorry I don't understand where you are headed with this.

Please explain more.

[ahoneycutt]

Thanks ItsMe.

I went through the tutorials you reference and I understand what it is doing I just don't understand how to apply it to this situation.

The threshold's are already set in a table. They are in this query simply to use for the calculation because I am having to evaluate the Plan and the amount against the threshold. I appreciate your help but you might was well be speaking another language because I do not know how to do any of the things you are telling me. I don't know how to create a formula using variables. I am just out of my league. Was hoping to get help but the help is to hard to understand. Thanks for trying.

[ItsMe]

...I went through the tutorials you reference and I understand ...
I don't know how to create a formula using variables...
Thanks for trying.

As I mentioned, it will take a while to go through the tutorials.

[June7]

Post your db for analysis so we can get a better understanding of structure.

Follow instructions at bottom of my post.

[IrogSinta]

Is the only thing different between the 17 bonus tiers, the Plan# and the field being compared to? In your sample of 2 plans, you have 3 possibilities with each plan and in each one you compare a certain field (i.e. EB & Direct Control) against other fields; however, it seems that your comparison expressions are similar. Will this be the case for all 17 plans?

Ron