New to Case statements

[Gina Maylone]

Hi Everyone!



I admit this is my first attempt at writing case statements, and I am not sure I have my head around it. I am trying to calculate various measurements; ie: square feet, cubic feet, linear feet, cubic feet, cubic yards. Is my statement correct? My numbers are not coming out right at all!
Thank you in advance.

Private Sub Height_AfterUpdate()
    On Error GoTo errHandler

    Select Case MeasurementType

Case "cuyd"
TotalMeasurement = Length * Width * Height / 27
'TotalMeasurement = Int(-100 * [TotalMeasurement]) / -100

Case "lf"
TotalMeasurement = Length * 1
'TotalMeasurement = Int(-100 * [TotalMeasurement]) / -100

Case "sqft"
TotalMeasurement = Length * Width
'TotalMeasurement = Int(-100 * [TotalMeasurement]) / -100

Case "cuft"
TotalMeasurement = Length * Width * Height
'TotalMeasurement = Int(-100 * [TotalMeasurement]) / -100


Case "sqyd"
TotalMeasurement = Length * Width / 9
'TotalMeasurement = Int(-100 * [TotalMeasurement]) / -100



End Select

Dim numvalue As Double
numvalue = [TotalMeasurement]
If (numvalue - Int(numvalue)) = 0 Then
Exit Sub
Else
numvalue = Int(numvalue) + 1
'MsgBox numvalue ' display the new value

'[Value1] = numvalue


End If

Exit Sub

errHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
           VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

End Sub

[pbaldy]

The structure seems correct. Is TotalMeasurement a variable or textbox? What values are in the various fields and what is the result?

[Gina Maylone]

The structure seems correct. Is TotalMeasurement a variable or textbox? What values are in the various fields and what is the result?

TotalMeasurement is a textbox. So, my measurement = cuft, and I enter 10 (L) 10(W) 10(h) and get 1401000. UGH.

[pbaldy]

Can you attach the db here?

[Gina Maylone]

Ok, I finally got it down to a size I could attach. Thanks for taking the time!!

[pbaldy]

How do I recreate the problem? Offhand, it looks like the data types of the fields are text. I'd use a numeric data type if you want to do numeric math on them.

[Gina Maylone]

Yes, I thought that too, however when I changed them to number, I got the same answer. Go into Contacts and Bids and in the tab control go to bid details.

[pbaldy]

I should have thought of this. I think your problem is reserved words. If you step through the code, Width is the wacky number, and I think it's because it's picking up the width property of the form rather than your field. You can work around it using bang:

TotalMeasurement = Me![Length] * Me![Width] * Me![Height]

But personally I'd change the field names (both width and height, since both are reserved words). I'd still use numeric data types.

[Gina Maylone]

I've changed the field names etc....that is so weird, I've been using length x width forever to calculate square feet with no problem. But when I decided to add cubic measurements and case statements, Access lost its mind!

Thank you again Paul - you always manage to save my butt.

[pbaldy]

Happy to help! One problem with reserved words is that sometimes Access will make the correct assumption, so you may not realize the problem until later.