Formula Rounding Problem

[jasonmv22]

*Please note - I am not the creator or manager of this access database, just an end user with no real knowledge of access whatsoever. Someone created this database for several sections of our company to input data into and produce reports, however there is one major issue with it that the creator can't seem to figure out. I just want to ask the experts here if a solution actually exists within access or if we will have to figure something else out.*



We're having an issue with formulas and how the products are rounded within access. I work in aviation, where flight duration is an important stat. We report flight duration to 1 decimal place. It is not a stat we manually input; we input the take-off and land time and the system calculates duration (ex. a take-off of 0900 and land of 1030 results in 1.5 hour duration).

The problem we are having is how the system rounds when the second decimal place is a 5. To follow in line with how we have always calculated flight duration, we need the system to round UP when the minute duration is under 30 and DOWN when minute duration is over 30.

For example, if a flight is 1 hour 27 minutes (1.45 hours) we need access to round UP to 1.5 duration. If a flight is 1 hour 33 minutes (1.55 hours) we need access to round DOWN to 1.5.

Does access have the ability to customize how it rounds in the way we need it to, or do you have to choose only either rounding up or rounding down?

Thank you!!

[orange]

Here is info on Access's rounding.

More info here from Gustav

[pbaldy]

I suspect you may be running into "bankers rounding", described near the bottom here with a potential solution:

http://allenbrowne.com/round.html

[CJ_London]

We need the system to round UP when the minute duration is under 30 and DOWN when minute duration is over 30.

to be clear - if flight time is 1:55 - you want that to round down to 1:30 (1.5) or flight time is 1:05 - that is to be rounded up to 1:30 (1.5)?

And you are not using 6 minute intervals? so you don't want 1.1, 1.2 etc

at the end of the day, it's only maths

[June7]

My approach to deal with rounding issue:

Function RRound(FieldValue As Variant, Optional intPos As Integer = 0) As Variant
'--------------------------------------------------
' Function RRound() rounds value to designated decimal position.
' If argument does not contain data, RRound returns null.
' Use because intrinsic Round uses even/odd (banker's) rounding.
' Also, Format and FormatNumber functions don't use even/odd but
' generate a string result which is often inconvenient.
'--------------------------------------------------
Dim strZeros As String
Dim i As Integer
If intPos = 0 Then
    strZeros = 0
Else
    For i = 1 To intPos
        strZeros = strZeros & 0
    Next
    strZeros = "0." & strZeros
End If
RRound = IIf(Not IsNull(FieldValue), Val(Format(FieldValue, strZeros)), Null)
End Function