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.
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
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
Of course, I make sure that [C] is not null or 0 first!