Results 1 to 7 of 7
  1. #1
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43

    Best way to handle decimal values?

    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


    Code:
    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?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Need to declare variables as Double, not Single.

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

    Code:
    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
    

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43
    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

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,700
    Try this:
    Code:
    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

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.
    Attached Files Attached Files

  6. #6
    smaier is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2020
    Posts
    43
    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!

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Be aware that Format property setting does not change the saved value. It is only a display setting. Calculations would use actual saved value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to handle missing values when cleaning data?
    By TerraEarth in forum Programming
    Replies: 1
    Last Post: 04-10-2018, 05:25 PM
  2. Replies: 2
    Last Post: 11-19-2012, 05:42 PM
  3. Decimal Values on a form
    By cap.zadi in forum Forms
    Replies: 1
    Last Post: 09-04-2011, 02:39 AM
  4. Replies: 1
    Last Post: 03-09-2006, 12:12 PM
  5. Get the sum of decimal values - weird results
    By BengtCarlsson in forum Queries
    Replies: 2
    Last Post: 02-10-2006, 04:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums