Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unhappy 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. #2
    Join Date
    Oct 2003
    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
    End If
    End Function

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

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


Posting Permissions

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