Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 97 (Rounding perhaps?)

    Having a problem with Access 97 that may be rounding.

    The fields that are involved in this problem are:

    [Tax}
    [CompleteTotal]
    [TotalPmt]
    Balance

    Formula in Balance field of Query is:

    Balance: CCur([CompleteTotal]-[TotalPmt])

    What I need is for Balance to display any remaining balance or overpayment. The only problem is that many of the records are displaying

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access 97 (Rounding perhaps?)

    Hi, CD.

    Floating point math is the actual problem, but you have a couple of choices in handling it. You can force the currency format onto your calculation of Tax by wrapping that expression in a ccur() function before you subtract it from CompleteTotal, or you can use Round() to get the Tax result down to 2 decimal places so that you don't have to worry about the other two decimals in the currency format. Even using the currency format, it's entirely possible to come up with a penny difference which is really a fractional cent difference rounding up for display, so I'd recommend forcing the round.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 (Rounding perhaps?)

    Charlotte I don't know why but I never have much luck using Round(). Can you perhaps send me an example of how you would use this on the tax field.

    Thanks


    "CD"

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access 97 (Rounding perhaps?)

    Tax: Round(([Casket]+[TotalSectionC])/2*[TAXRATE]+([TotalSectionA]+[TotalSectionB]-[Casket])*[TAXRATE],2)
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 (Rounding perhaps?)

    Thanks Charlotte, but it doesn't work. I keep getting an undefined "Round" function message. I didn't think Round was even available in Access 97. I tried attaching it to the query. CCur didn't seem to do it either.

    I also tried =IIf([Balance]<0,0,[balance])as the control source for an unbound control on the data entry form as they need to see this on the form rather than just on reports. That gets rid of the odd -.01, but then if there is a credit balance from an overpayment, it won't show.

    Any other suggestions?

    Thanks again.


    "CD"

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 (Rounding perhaps?)

    I haven't really solved this problem yet - your suggestion doesn't seem to work (and I am probably the problem) as I just don't do well with Visual Basic.

    I had added an unbound control with a formula like

    IIf([Balance]<1,0,[Balance) thinking I would suppress the -.01. HOWEVER, that also suppresses credit balances which I had not thought about so I've had to take that out. At this point all I want to do is be certain any credit balance does show up but that the .01 is suppressed on the data entry screen.

    I have tried a whole bunch of IIF's to try to get this to work but I am putting something in the wrong place because each time I do this, it suppresses ALL credit balances, rather than just those that are -.01.

    Any thoughts on this?

    Thanks again.


    "CD"

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access 97 (Rounding perhaps?)

    Are you saying the code doesn't work or that you're not getting the result you wanted? Are you getting an error message?

    Where do you want the value to show up as zero, in a query, on a form, or what? If you round the Tax calculation result down to two places and you still come up with a penny difference, then you may need to suppress rounding up, which is the default in that piece of code. Can you post some examples of the numbers being passed to the function and the CompleteTotal value you're subtracting it from? If CompleteTotal hasn't been formatted as currency already, you may be having a problem with floating point addition, which can also give you number creep.
    Charlotte

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access 97 (Rounding perhaps?)

    ED: 6/21/2001 to correct code error - Charlotte

    Sorry, I forgot that Round() was in Excel 97 but not in Access 97. In that case, here's a routine that I wrote for Access 97 that will allow you to round a number to a specified number of decimal places. Try using it on your Tax calculation instead of the Round() function. Try it like this:

    This assumes you want to round up.
    <pre>Tax = FixedNum(([Casket]+[TotalSectionC])/2*[TAXRATE]+ _
    ([TotalSectionA]+[TotalSectionB]-[Casket])*[TAXRATE],2)</pre>

    <pre>Public Function FixedNum(ByVal dblNumber As Double, _
    ByVal intPlaces As Integer, _
    Optional blnRoundUp As Boolean = True) As Variant
    <font color=448800>' Created by Charlotte Foust
    ' 3/5/99
    ' Similar to Excel Fixed() function
    ' Negative numbers rounded to left of decimal point
    '
    ' Modified 4/8/99 -- handle intPlaces=0
    ' Modified 7/31/2000 -- handle fewer decimal
    ' places than specified in intPlaces.
    ' Modified 3/26/2001 -- cleaned up code and added comments
    ' Modified 6/21/2001 -- corrected handling of whole numbers</font color=448800>
    Dim dblNum As Double <font color=448800>'holds whole number left of decimal</font color=448800>
    Dim strDecimal As String <font color=448800>'holds decimal portion of number _
    converted to string</font color=448800>
    Dim lngDecimal As Long <font color=448800>'holds decimal converted from string _
    and rounded to specified places</font color=448800>
    Dim intDecPos As Integer <font color=448800>'holds position of decimal point in _
    original string</font color=448800>
    Dim intRounder As Integer <font color=448800>'holds the number that determines rounding</font color=448800>
    Dim strNum As String <font color=448800>'holds the double converted to string</font color=448800>
    Dim blnNegative As Boolean <font color=448800>'holds true if number is negative</font color=448800>
    Dim blnTruncate As Boolean<font color=448800>'holds true if intPlaces = 0</font color=448800>
    Dim dblReturn As Double <font color=448800>'holds the return value of FixedNum</font color=448800>

    <font color=448800>'set the Truncate and Negative flags</font color=448800>
    If intPlaces = 0 Then
    blnTruncate = True
    End If <font color=448800>'intPlaces = 0</font color=448800>
    If dblNumber < 0 Then
    blnNegative = True
    End If <font color=448800>'dblNumber < 0</font color=448800>

    <font color=448800>' Extract the whole number
    ' from the double</font color=448800>
    dblNum = Fix(dblNumber)
    <font color=448800>' convert the double to a string</font color=448800>
    strNum = CStr(dblNumber)
    <font color=448800>' find the decimal point in the string</font color=448800>
    intDecPos = InStr(strNum, ".")

    <font color=448800>' if intPlaces<>0 extract the decimal portion</font color=448800>
    If Not blnTruncate And intDecPos <> 0 Then <font color=red>'<--ED: 6/21/2001</font color=red>
    <font color=448800>' extract the decimal portion of the double</font color=448800>
    strDecimal = Right(strNum, Len(strNum) - intDecPos)
    lngDecimal = CLng(Left(strDecimal, intPlaces))
    Else <font color=448800>Not blnTruncate And intDecPos <> 0</font color=448800>
    strDecimal = "0"
    lngDecimal = CLng(strDecimal)
    End If <font color=448800>Not blnTruncate And intDecPos <> 0</font color=448800>

    <font color=448800>' extract the rounding determinant from the decimal portion</font color=448800>
    If Len(strDecimal) >= intPlaces + 1 Then
    intRounder = CInt(Mid(strDecimal, intPlaces + 1, 1))
    Else <font color=448800>'Len(strDecimal) >= intPlaces + 1</font color=448800>
    intRounder = 0
    End If 'Len(strDecimal) >= intPlaces + 1

    <font color=448800>' apply rounding unless blnRoundup = False</font color=448800>
    Select Case blnRoundUp
    Case True <font color=448800>'blnRoundUp</font color=448800>
    If intRounder >= 5 Then
    lngDecimal = lngDecimal + 1
    End If 'intRounder >= 5
    Case Else <font color=448800>'blnRoundUp</font color=448800>
    End Select <font color=448800>'Case blnRoundUp</font color=448800>

    <font color=448800>' If intPlaces <> 0, add the decimal
    ' portion back to the base number</font color=448800>
    If Not blnTruncate Then
    <font color=448800>' if the number is negative,
    ' subtract the decimal amount</font color=448800>
    If blnNegative Then
    dblReturn = dblNum - (lngDecimal / 10 ^ intPlaces)
    Else <font color=448800>'blnNegative</font color=448800>
    dblReturn = dblNum + (lngDecimal / 10 ^ intPlaces)
    End If <font color=448800>'blnNegative</font color=448800>
    Else <font color=448800>' Not blnTruncate</font color=448800>
    Select Case blnRoundUp
    Case True <font color=448800>'blnRoundUp</font color=448800>
    <font color=448800>'apply rounding</font color=448800>
    If CInt(Left(strDecimal, 1)) >= 5 Then
    If blnNegative Then
    dblReturn = dblNum - 1
    Else <font color=448800>'blnNegative</font color=448800>
    dblReturn = dblNum + 1
    End If <font color=448800>'blnNegative</font color=448800>
    Else <font color=448800>'CInt(Left(strDecimal, 1)) >= 5</font color=448800>
    dblReturn = dblNum
    End If 'CInt(Left(strDecimal, 1)) >= 5
    End Select <font color=448800>'Case blnRoundUp</font color=448800>

    <font color=448800>'set the return value</font color=448800>
    dblReturn = dblNum
    End If <font color=448800>' Not blnTruncate</font color=448800>

    FixedNum = dblReturn
    End Function <font color=448800>'FixedNum(ByVal dblNumber, _
    ByVal intPlaces, _
    Optional blnRoundUp) As Variant</font color=448800></pre>

    Charlotte

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access 97 (Rounding perhaps?)

    First of all, I hope all the fields you mentioned are currency fields. Then you have to remember that currency fields keep up to 4 decimal positions, and though you may only display 2 of them, the other 2 are still there. This is usually the case with Tax calculations. I use this rounding formula to get rid of the last 2 digits.

    [Tax] = CCur(CCur( [Tax] ) / 100) * 100

    Notice I'm continually converting to currency. This is because Access has a habit making the result of a division a floating point decimal.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •