Limitation to Switch Statement

Posted on 2014-01-29
Last Modified: 2014-01-30
Experts. I have a query with multiple switch function statements. Below is one of those statements. Just recently I'm trying to add the tblProperty.RL_PC1 = 54.... and it's giving me a too complex error.

In searching the web I'm reading that there isn't a limit in the switch function. Anyone have any alternatives to this kind of statement. It used to be a nested iif statement. That got too complex so I changed it to a switch statement. Now that's too complex. I've considered a table and using lookup function but I'm under a time crunch and a quick fix in the mean time. Any ideas?

Switch(tblProperty.RL_PC1=65,([1Occ50]*0.65*2*0.025)-0.5,tblProperty.RL_PC1=60,([1Occ50]*0.6*2*0.025)-0.5,tblProperty.RL_PC1=57,([1Occ50]*0.57*2*0.025)-0.5, tblProperty.RL_PC1=54,([1Occ50]*0.54*2*0.025)-0.5, tblProperty.RL_PC1=53,([1Occ50]*0.53*2*0.025)-0.5,tblProperty.RL_PC1=50,([1Occ50]*0.5*2*0.025)-0.5,tblProperty.RL_PC1=45,([1Occ50]*0.45*2*0.025)-0.5,tblProperty.RL_PC1=40,([1Occ50]*0.4*2*0.025)-0.5,tblProperty.RL_PC1=35,([1Occ50]*0.35*2*0.025)-0.5,tblProperty.RL_PC1=33,([1Occ50]*0.33*2*0.025)-0.5,tblProperty.RL_PC1=30,([1Occ50]*0.3*2*0.025)-0.5,tblProperty.RL_PC1=28,([1Occ50]*0.28*2*0.025)-0.5,tblProperty.RL_PC1=25,([1Occ50]*0.25*2*0.025)-0.5,tblProperty.RL_PC1=20,([1Occ50]*0.2*2*0.025)-0.5,tblProperty.RL_PC1=100,([1Occ50]*2*0.025)-0.5) AS 0Rent1,
Question by:acramer_dominium
  • 4
  • 3
LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 500 total points
ID: 39818187
Since all of your criteria seem to involve the value of the RL_PC1 field and the only thing that seems to differ between the functions is the first multiplier, which happens to be the value of [RL_PC1]/ 100, I would simply use this formula

0Rent1: (([tblProperty].[RL_PC1 / 100) * [1Occ50] * 2 * .025) - 0.5
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39818218
BTW,  It is really easy to spot the similartities when you format the statement like:

) AS 0Rent1,

Another way to look at that (if those multipliers had not been equal to [RL_PC1]/100, you might have tried:

SWITCH(tblProperty.RL_PC1 = x, y,
               tblProperty.RL_PC1 = z, a) * [1Occ50] * 2 * .025 - 0.5

Where you would not have the entire function evaluated inside the switch, you would simply identify the value of the multiplier.

Lastly, you could create a lookup table with the values of RL_PC1 and Multiplier and either use DLOOKUP to lookup the value of the Multiplier for a given value of [RL_PC1], or better yet, join the lookup table to the rest of your query on the RL_PC1 field and simply use the multiplier in the equation.

Author Comment

ID: 39819206
Thank you for the suggestion! I would still need the if statement to check for the PC_1 value (65,60,57,etc).

How would that factor into: 0Rent1: (([tblProperty].[RL_PC1 / 100) * [1Occ50] * 2 * .025) - 0.5
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39819348
Based on the values in your SWITCH statement, you don't need to check for the value of [RL_PC1], you simply need to divide that value by 100

Notice that when RL_PC1 = 65, the multiplier (value right behind [1Occ50] is .65, same for RL_PC1 = 57, value is .57, so all you need to do at least for this particular SWITCH statement is remove the switch and replace it with a single function.

ORent1: [tblProperty].[RL_PC1] / 100 * [1Occ50] * 2 * .025 - 0.5

You don't really need any parentheses in there because of the precedence of numeric operators.

Author Comment

ID: 39819415
I see what you are saying. I will give that a shot and let you know how it turns out.

Thank you!!

Author Closing Comment

ID: 39821585
This worked perfectly for what I needed. I inherited this from a previous programmer and didn't see that scheme throughout the statements.

LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39821600
Glad I could help.

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now