Select case Limitations

[AMD3000]

Hi everyone,

I am wondering if there is a limitations on how many cases you can have in a Select Case. I want to write a function where i can calculate some fields but i have around 250 types of windows, Which I will have to have around 250 cases.. Am I nuts?????

Any ideas, suggestions would be appreciated

Thanks
AMD3000

 

[RV]

i have around 250 types of windows, Which I will have to have around 250 cases

Sounds to me you could do with a bit of normalization
A SELECT CASE per type doesn't make sense.

RV

 

[AMD3000]

I am trying to write an order entry application. We are a Windows company. We have something like 1200 types of windows, terrace doors, screens, etc. But around 200 to 250 windows Series to calculate how the extrusions are going to be cut and the glass. All those calculations have a formula by Windows series.. That is why I was trying to figure out if I can have that many case stamens.

 

[RuralGuy]

AFAIK there is *no* limitation to a Select Case structure but it still sounds like a normalization problem to me too.

 

[Pat Hartman]

You don't need a Select Case if you store the choices in a table. Base your form on a query that joins the main table to the lookup table to find what you need.

 

[AMD3000]

Thanks you guys. I am pretty new at this so I am not sure if I am doing this right. See I have a form where information about an order would be enter, then I have a Tab that would have the detail information about the order( so I have two table one for order and one for details) When they enter the details of expecific window, they need to enter Heigth and width and Vent and many other things. But with the H, W and Vent I need to calculate 13 fields that are need to build this window. So I thought I would create a function so when:
Select Case SERIES
Case 100, 140, 200, 240
UpperW = DWIDTH - 3.9375
Case 125, 225
UpperW = DWIDTH / 2 - 2.3125
Case 130, 230
UpperW = (DWIDTH - (2 * DVENT)) - 0.125
.
.
end Select

this is just for one of the field.

Please if there is a better way. I Not sure how to do it with a table.

thanks,
AMD3000

 

[Pat Hartman]

The Eval() function is extremely powerful. I have one application where I store procedure names in a table and use the Eval() function to run them. That lets rec1 run procA, B, and C and rec2 run procD, C, and F. You might actually be able to store the calculations in the table and use the Eval() to run them.

Or if the calculations are limited in formula with only the variable changing, you could store calcType and variable.

Or failing that, stick with the Case statement. However, when using a long case statement, you will get better performance by placing the most common values at the top of the list. If you have one window type that represents 20% of your business, you want it to be at the top of the list rather than the bottom since Access evaluates the list in order and stops as soon as it finds a matching value.

 

[DB7]

Great Advice, Pat!