# Thread: Access 97 (Rounding perhaps?)

1. ## 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. ## 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.

3. ## 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"

5. ## 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. ## 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.

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. ## 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.

8. ## 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>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 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>

9. ## 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.

#### Posting Permissions

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