I need to return 1 value based on an equation

[Simmo160]

I'm trying to do a form that returns the size of a circuit breaker when the kVA and voltage are entered, with a calculation behind the form working out the current.

Current = (kVA * 1000) / (Volts * 1.732)

However, I cannot find out how to do this, except by calling consecutive subs.

So, for instance, Current could be 17 Amps, 26 Amps, 45 Amps, anywhere up to 6300 Amps.

Circuit breakers are sized 10, 16, 20, 25, 32, 40, 50, 63, 100, 125, 160, 250, 400, 630...6300, so there are a lot of variables.

The only way i can get this to work is:

Call CBSize10
 
Private Sub CBSize10()
    If [Current] <= 10 Then
        Text39.Value = "10"
    Else
        Call CBSize16
    End If
End Sub

This would then need to go through 23 more subs if the current is large.

I've tried IIFs, but that's a no go. Any ideas?

[Rabbit]

Please use code tags when posting code or formatted data.

You can use a select case statement to choose the right breaker. For example:

Select Current
   Case 0 To 10
      BreakerSize = 10
   Case 11 To 16
      BreakerSize = 16
   ......
   Case Else
      BreakerSize = -1
End Select

[NeoPa]

I would set up a table of the circuit-breakers; determine the current resulting from the calculation; Use a query (or DLookup()) to determine which record is returned that matches the maximum value where Current <= the value in the record.

[Simmo160]

Thank you for the responses. I'll give them a go over the next couple of days.

Sorry if I posted incorrectly. This is my first question, so bit a newb.

[NeoPa]

Let us know how you get on.

PS. As long as you go forward and learn from any posted instructions, so not repeating past mistakes, we're more than happy. If we expected you to get it perfect first time we'd be dumb - and we try to avoid that ;-)

[Simmo160]

Okay, so I created a sub for the Case Select, and that works as I want it to. I can also see the benefits for a separate table and DLookup if I was entering the sizes from scratch. So thank you both. Happy chappy.