Solved

Limitation to Switch Statement

Posted on 2014-01-29
7
287 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
[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
  • Learn & ask questions
  • 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Display field if column exists 7 35
Troubleshooting Save_Record_Click() VBA 6 21
Access vs Access runtime 6 16
calculate running total 8 10
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

733 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