Solved

Limitation to Switch Statement

Posted on 2014-01-29
7
278 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

743 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

10 Experts available now in Live!

Get 1:1 Help Now