Best way to handle decimal values?

[smaier]

Hello, all. This question kind of spans a few possible categories so I wasn't sure which one to pick. If this belongs elsewhere I can re-post in a different category forum.



The problem I am having is when I am trying to compare the value of a bound textbox to the calculated value pulled from 2 unbound textboxes (all of which have 3 decimal places). This is to see if the dimension of a part entered by a user meets the dimensional requirements or tolerances. The objects I am using are:

• (unbound textbox) txtNominal, formatted as General Number in properties
• (unbound textbox) txtPlus, formatted as General Number in properties
• (unbound textbox) txtMinus, formatted as General Number in properties
• (bound textbox) txtDimension, formatted as Decimal in the linked table, control source is [Entered dimension]
• (Command button) Command8 to run the following VBA

Private Sub Command8_Click()
Dim nominal As Single
Dim plus As Single
Dim minus As Single
nominal = txtNominal.Value
plus = txtPlus.Value
minus = txtMinus.Value
txtDimension.BackColor = vbWhite
If [Entered dimension] > (nominal + plus) Then
        txtDimension.BackColor = vbYellow
        MsgBox "The given dimension is over tolerance"
        MsgBox "Tolerance high = " & nominal + plus & vbNewLine & "Dimension = " & [Entered dimension]
        Exit Sub
    ElseIf [Entered dimension] < (nominal - minus) Then
        txtDimension.BackColor = vbYellow
        MsgBox "The given dimension is below tolerance"
        MsgBox "Tolerance high = " & nominal - minus & vbNewLine & "Dimension = " & [Entered dimension]
        Exit Sub
    End If
End Sub

What's driving me nuts is I am getting instances where a dimension entered into the bound textbox appears to be within tolerance but the VBA result is telling me it's not. Pretty much when the entered dimension is on the high. For example, if txtNominal's alue is .436, txtPlus's value = .003, txtMinus's value is .003, and the user puts .439 into the bound textbox [Entered dimension] and clicks the command button to run the code, it's telling them that .439 is over the tolerance. Which at only 3 decimal places it's not over tolerance.

What I am needing is a way to force any stored values to not exceed 3 decimal places. Either programmatically or at the property level. I'm leary of using Round() due to the whole bankers rounding thing.

Any ideas or am I doing something fundamentally incorrect?

[June7]

Need to declare variables as Double, not Single.

Banker's rounding is why I use a custom rounding function.

Function RRound(FieldValue As Variant, Optional intPos As Integer = 0) As Variant
'--------------------------------------------------
' Function RRound() rounds value to designated decimal position.
' If argument does not contain data, RRound returns null.
' Use because intrinsic Round uses even/odd (banker's) rounding.
' Also, Format and FormatNumber functions don't use even/odd but
' generate a string result which is often inconvenient.
'--------------------------------------------------
Dim strZeros As String
Dim i As Integer
If intPos = 0 Then
    strZeros = 0
Else
    For i = 1 To intPos
        strZeros = strZeros & 0
    Next
    strZeros = "0." & strZeros
End If
RRound = IIf(Not IsNull(FieldValue), Val(Format(FieldValue, strZeros)), Null)
End Function

[smaier]

Thank you for your reply, June7, that function is pretty slick!

I'm still having the same problem, though. If I enter .4389 the code doesn't say the dimension is over tolerance. If I enter .439 it says it's out of tolerance. Entirely possible I am not using the function properly.

I've attached the db. If that helps.TestDB.zip

[davegri]

Try this:

Option Compare Database
Option Explicit


Private Sub Command8_Click()


Dim nominal As Single
Dim plus As Double
Dim minus As Double
Dim dimension As Double
Dim hi As Double
Dim lo As Double
nominal = RRound(txtNominal.Value, 4)
plus = RRound(txtPlus.Value, 4)
minus = RRound(txtMinus.Value, 4)
dimension = RRound(txtDimension, 4)


hi = RRound(nominal + plus, 4)
lo = RRound(nominal - minus, 4)


txtDimension.BackColor = vbWhite


If dimension > (hi) Then
        txtDimension.BackColor = vbYellow
        MsgBox "The given dimension is over tolerance"
        MsgBox "Tolerance high = " & hi & vbNewLine & "Dimension = " & dimension
    ElseIf dimension < (lo) Then
        txtDimension.BackColor = vbYellow
        MsgBox "The given dimension is below tolerance"
        MsgBox "Tolerance high = " & lo & vbNewLine & "Dimension = " & dimension
        Exit Sub
    End If


End Sub


Function RRound(FieldValue As Variant, Optional intPos As Integer = 0) As Variant
'--------------------------------------------------
' Function RRound() rounds value to designated decimal position.
' If argument does not contain data, RRound returns null.
' Use because intrinsic Round uses even/odd (banker's) rounding.
' Also, Format and FormatNumber functions don't use even/odd but
' generate a string result which is often inconvenient.
'--------------------------------------------------
Dim strZeros As String
Dim i As Integer
If intPos = 0 Then
    strZeros = 0
Else
    For i = 1 To intPos
        strZeros = strZeros & 0
    Next
    strZeros = "0." & strZeros
End If
RRound = IIf(Not IsNull(FieldValue), Val(Format(FieldValue, strZeros)), Null)
End Function

[ssanfu]

Here is what I came up with. Change the Entered Dimension to see the formatting.
You can set the format for the unbound text boxes FORMAT property to "Fixed" or "Standard" and the DECIMAL PLACES property to 4 to always see 4 decimals.

[smaier]

June7 and davegri, thank you so much! I have never messed with custom functions before. That one is really clever and opens my mind to having more control over values, amongst other things.

ssanfu, thank you as well! When I was poking properties I did see the Fixed option, but I couldn't find a clear definition of how that worked. In hindsight it seems so clear.

Again, thank you all for the help. I don't know if other people get as bummed out when the problem at hand is getting the better of them. I thought there is no way my requirements are that unusual. It's not like I'm trying to get access to do finite element analysis or something out of left field. This seemed like a normal thing to need and yet I just couldn't figure it out.

Not only are there multiple solutions to the problem I was having, but I learned a fe things in the process. Thank you all again!

[June7]

Be aware that Format property setting does not change the saved value. It is only a display setting. Calculations would use actual saved value.