# Thread: Problems with calculation in a query, rounding issue?

1. Registered User
Join Date
Jan 2004
Posts
31

## Unanswered: Problems with calculation in a query, rounding issue?

I'm having a problem with calculated fields in a query. I'm trying to average two fields so I just added the two together and divided by 2. But when I run the query it isn't showing the answer that you would expect. For example, (4+3)/2 gives the result as 4.00 instead of 3.50 but (2+3)/2 gives 2.00 instead of 2.50. There doesn't seem to be a pattern with it because it's not always rounding up like you would expect it to. I'm getting very frustrated with this...does anyone have any idea why this is doing this? Any help would be appreciated.

2. Registered User
Join Date
Oct 2003
Location
Posts
574
I made my own rounding function in a module which accepts the number to be rounded and the number of decimal places. In my query I have a column: BRound([A] + [B] )/ [C],2) as CalcAmt

Public Function BRound(TheNumber As Variant, SigDigits As Integer) As Double
Dim RoundUp As Integer, stNum As String
RoundUp = 0
If IsNull(TheNumber) Or Abs(TheNumber) < 0.0001 Or TheNumber = "" Then
BRound = 0
Else
stNum = Right(CStr(Fix(CStr(TheNumber * 10 ^ (SigDigits + 1)))), 1)
If stNum >= "5" Then RoundUp = 1
stNum = TheNumber * 10 ^ SigDigits
stNum = Fix(stNum) + RoundUp
BRound = stNum / 10 ^ SigDigits
End If
End Function

Of course, I make sure that [C] is not null or 0 first!

3. Registered User
Join Date
Oct 2003
Location
New York
Posts
23
Just a thought...make sure your decimal places are set to 2 or auto...if the decimal is set to 0, it will round...you can always use a function...i.e.

format([field1]+[field2],"fixed")

#### Posting Permissions

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