acramer_dominium
asked on
Limitation to Switch Statement
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,([1Occ5 0]*0.65*2* 0.025)-0.5 ,tblProper ty.RL_PC1= 60,([1Occ5 0]*0.6*2*0 .025)-0.5, tblPropert y.RL_PC1=5 7,([1Occ50 ]*0.57*2*0 .025)-0.5, tblProperty.RL_PC1=54,([1O cc50]*0.54 *2*0.025)- 0.5, tblProperty.RL_PC1=53,([1O cc50]*0.53 *2*0.025)- 0.5,tblPro perty.RL_P C1=50,([1O cc50]*0.5* 2*0.025)-0 .5,tblProp erty.RL_PC 1=45,([1Oc c50]*0.45* 2*0.025)-0 .5,tblProp erty.RL_PC 1=40,([1Oc c50]*0.4*2 *0.025)-0. 5,tblPrope rty.RL_PC1 =35,([1Occ 50]*0.35*2 *0.025)-0. 5,tblPrope rty.RL_PC1 =33,([1Occ 50]*0.33*2 *0.025)-0. 5,tblPrope rty.RL_PC1 =30,([1Occ 50]*0.3*2* 0.025)-0.5 ,tblProper ty.RL_PC1= 28,([1Occ5 0]*0.28*2* 0.025)-0.5 ,tblProper ty.RL_PC1= 25,([1Occ5 0]*0.25*2* 0.025)-0.5 ,tblProper ty.RL_PC1= 20,([1Occ5 0]*0.2*2*0 .025)-0.5, tblPropert y.RL_PC1=1 00,([1Occ5 0]*2*0.025 )-0.5) AS 0Rent1,
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=
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
How would that factor into: 0Rent1: (([tblProperty].[RL_PC1 / 100) * [1Occ50] * 2 * .025) - 0.5
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.
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.
ASKER
I see what you are saying. I will give that a shot and let you know how it turns out.
Thank you!!
Thank you!!
ASKER
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!
THANK YOU!
Glad I could help.
Switch(
tblProperty.RL_PC1=65,([1O
tblProperty.RL_PC1=60,([1O
tblProperty.RL_PC1=57,([1O
tblProperty.RL_PC1=54,([1O
tblProperty.RL_PC1=53,([1O
tblProperty.RL_PC1=50,([1O
tblProperty.RL_PC1=45,([1O
tblProperty.RL_PC1=40,([1O
tblProperty.RL_PC1=35,([1O
tblProperty.RL_PC1=33,([1O
tblProperty.RL_PC1=30,([1O
tblProperty.RL_PC1=28,([1O
tblProperty.RL_PC1=25,([1O
tblProperty.RL_PC1=20,([1O
tblProperty.RL_PC1=100,([1
) 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.