how to use the select case

[Sudha]

Hi,
I have a query which needs to find the value of GeneratorKVA.
I have two other fields in the query, total elec load in kVA and required kVA.
Required kVA is calculated as Required kVA:[total elec load in kVA]/.8
if Required kVA = 0, then Generator kVA=0
if Required kVA>0 and <=20 then Generator kVA=20
if Required kVA>20 and <=30 then Generator kVA=30 and so on till Required kVA<=2000.
How do I use the select case for this

 

[Alc]

Select Case kVA
     Case = 0
           [Generator kVA] = 0
 
     Case > 0 and <= 20 
           [Generator kVA] = 20

     Case > 0 and <= 30 
           [Generator kVA] = 30
End Select

and so on.

Seems a very long-winded way of doing it.
You'll need dozens of Cases. Plus, if the generator max changes in the future to be 1,000,000 are you going to code in the thousands of Case statements necessary?

In an attempt to save you work, have a search on the forum (or even Access Help) for "\" under arithmetic operators. I think a bit of tinkering with this operator might be helpful for you.

 

[Brianwarnock]

If the sample data is anything to go on then why do you need a selection at all Generator kVA= max required kVa

Brian

I recheck realised that was not what was wanted so how about
gen = (Round((kva / 10) + 0.5)) * 10

 

[raskew]

Hi -

If you are going to 2000, you'll end up with the world's longest Select
Case. Think we can do it with a one-liner, using the Switch() function

GeneratorKV: Switch(RequiredKVA = 0, 0, RequiredKVA <=20, 20, True, ((RequiredKVA\10)*10) + iif(RequiredKVA mod 10 <> 0, 10, 0))

We can test that (I replaced RequiredKVA with x) from the debug (immediate) window:

x = 29
? ((x\10)*10) + iif(x mod 10 <> 0, 10, 0)
30

x = 30
? ((x\10)*10) + iif(x mod 10 <> 0, 10, 0)
30

x = 31
? ((x\10)*10) + iif(x mod 10 <> 0, 10, 0)
40

HTH - Bob

 

[Brianwarnock]

Tried to Edit my post but the system hung, anyway I see Bob has come up with a solution.
Provided we are talking about increments of 10 then
gen = (Round((kva / 10) + 0.5)) * 10
should work.

Brian

 

[raskew]

Brian -

From what the OP said:

if Required kVA>20 and <=30 then Generator kVA=30

...then the groups run 21 - 30, 31 - 40, etc.

Your solution works great except when the user selects 30 or 40, etc.
then it bumps it up to the next group

Example:

x = 30 
? (Round((x / 10) + 0.5)) * 10
 40

I've tried playing with it but the Round() function has never been my strong suit and I've yet to come up with a workaround.

Best wishes - Bob

P.S. - You don't like my solution at Post #4?

 

[Brianwarnock]

Yes Bob your solution was fine, just wanted, hopefully,to correct the impression that my post 3 gave of me being a cretin.

BTW this
Sub kvas()
Dim kva As Integer
Dim gen As Integer
kva = 40
gen = (Round((kva / 10) + 0.5)) * 10
Debug.Print gen

End Sub

returns 40

But now that you mention it I seem to remember a recent thread where the user wanted .5 to round down and it didn't, very strange, you could always add 0.49


I can't get this inline ? thingy to work. I'm on Access 2002.
When I enter the ? it turns it to Print.

Brian

 

[raskew]

Brian -

This is indeed puzzling! Check these examples:

kva = 40
? (Round((kva / 10) + 0.5)) * 10
40

kva = 30
? (Round((kva / 10) + 0.5)) * 10
40

kva = 50
? (Round((kva / 10) + 0.5)) * 10
60


It appears that the result changes depending on whether
kva/10 returns an odd or an even number. If it returns
4 then the result will be 40. However, if it returns 3 or 5
the result will be 40 or 60.

The Help File offered no clue.

Bob

 

[Brianwarnock]

Yes you are correct so +0.49 it is , and as it should have been, but what a quirk.

I bet the OP is using your solution.

Brian

 

[gemma-the-husky]

try the int function (or fix - they are only different for negative numbers)
rather than a cint (which rounds)

1. assume all your kvas are discete integers (ie no decimals)

2. int((kva+9)/10 )*10 will generate multiples of 10

ie 30 will become 39 which goes back to 30
but 31+ will end up as 40

if you have decimals in the KVA, you will need to tweak this - i think adding 9.999 will work for anything.
the thnig is, the increment added to 30 needs to stay below 40, but the nicrement to any number larger than 30 needs to take the sum over 40 (as I understand it)



3, then you just need code to specials values less than 20

so try

Select Case kVA
     Case = 0
           [Generator kVA] = 0
 
     Case <= 20 
           [Generator kVA] = 20

     Case else 
           [Generator kVA] = int((kva+9)/10 )*10
End Select

 

[raskew]

Hi -

Thanks for that. You got around that pesky Round() function, which in my personal opinion is completely evil -- I never use it.

Re the discrete integers issue -- guess that's going to be the OP's call.

I took the OP's if Required kVA>20 and <=30 then Generator kVA=30 to mean just that and coded it accordingly.

30.9 is greater than 30, so it moves to the next group.
kva = 30.9
? ((kva\10)*10) + iif(kva mod 10 <> 0, 10, 0)
40

Brian's revised solution appears to work like mine:
kva = 40.9
? (Round((kva / 10) + 0.49)) * 10
50

whereas you pointed out, your solution will result in:
kva = 30.9
? int((kva+9)/10 )*10
30

Best Wishes -- Bob

 

[raskew]

Hey Sudha -

Are you out there?

Since this is your problem, it'd be good to hear from you re what you've seen so far!

Best Wishes - Bob

 

[Brianwarnock]

Hi Bob
The Round approach also works with non integers, but i'm not pushing it as the method. I find it difficult to believe that the generator kVa rises in increments of 10 at all levels after 20. But what do I know.

Brian

 

[Brianwarnock]

How about Dlookup.
Create a table of generator sizes say tblgen and field Gensize

Then Dlookup("[Gensize]","tblgen","Gensize >" & [kvareq]-1)
will return the next biggest size
Gensize
0
20
30
70

KVA.......returned
2..............20
20.............20
21.............30
42..............70


easily maintained and allows for gaps in generator size

Brian

 

[raskew]

OK, that looks good.

How about this.

1) I've created a table (tblGenLook) with one field (GenSize)

2) This query: The RequiredKVA based on the OP's

RequiredKVA is calculated as Required kVA:[total elec load in kVA]/.8

SELECT
    tblGenLook.GenSize
  , [GenSize]/0.8 AS RequiredKVA
  , Switch([RequiredKVA]=0,0,[RequiredKVA]<=20,20,True,([RequiredKVA]\10)*10)+IIf([RequiredKVA] Mod 10<>0,10,0) AS GenKVA
FROM
   tblGenLook

3) Returns this:

GenSize RequiredKVA GenKVA
0       0           0
20      25          30
30      37.5        40
70      87.5        90

Bob

 

[Brianwarnock]

RequiredKVA is calculated as Required kVA:[total elec load in kVA]/.8

I can't helping thinking I took my eye off the ball somewhere on this thread, I forgot all about that line,

Brian

 

[Sudha]

hi all,
Thank you very much.
I used the dlookup and it is working fine. thanks again.