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
  • Learn & ask questions
Solved

Limitation to Switch Statement

Posted on 2014-01-29
7
285 Views
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,
0
Comment
Question by:acramer_dominium
  • 4
  • 3
7 Comments
 
LVL 47

Accepted Solution

by:
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
0
 
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:

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

by:acramer_dominium
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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

Author Comment

by:acramer_dominium
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

by:acramer_dominium
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 47

Expert Comment

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

766 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