Solved

# Limitation to Switch Statement

Posted on 2014-01-29
Medium Priority
290 Views
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,
0
Question by:acramer_dominium
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3

LVL 48

Accepted Solution

Dale Fye earned 2000 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
0

LVL 48

Expert Comment

ID: 39818218
BTW,  It is really easy to spot the similartities when you format the statement like:

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,

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.
0

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
0

LVL 48

Expert Comment

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.
0

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!!
0

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.

THANK YOU!
0

LVL 48

Expert Comment

ID: 39821600
0

## Featured Post

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
###### Suggested Courses
Course of the Month10 days, 8 hours left to enroll