Should banker's rounding work on calculated figures?

[bushwacka666]

Hello all,

I am currently trying to grapple with the Round function in Access. I should start by saying that I know Access uses "banker's rounding" and this is actually what I want it to do, however I am finding that even this doesn't appear to be functioning as I want it to.

For example, let's take the figure 1.915 which I want to show to two decimal places. According to banker's rounding, this should therefore bring the figure to 1.92.

Indeed, if I were to refer to the figure directly, using Round(1.915,2) then 1.92 is exactly what I get. Great!

However, say I have a field in a query or report called TOTAL that is running a calculation based on values found in other fields, and now 1.915 is the result it provides here. In this instance, using Round([TOTAL],2) now gives me 1.91, not 1.92.

So is there any way I can make Access correctly use banker's rounding on a figure that was calculated from an expression? It seems like it always wants to round down, regardless of what the figure is.

 

[plog]

How is TOTAL being calculated? Can you show us that code and any subsequent downstream code?

My guess is TOTAL is rounding rounded values. For example:

1.114
2.224
3.334

If you add then round you get:
6.672
6.67

If you round then add you get:
1.11
2.22
3.33
6.66

I think your rounding too early for your TOTAL and it doesn't actually equal 1.915 when it gets to Round(TOTAL,2)

 

[Imb]

However, say I have a field in a query or report called TOTAL that is running a calculation based on values found in other fields, and now 1.915 is the result it provides here. In this instance, using Round([TOTAL],2) now gives me 1.91, not 1.92.

The value of Total is displayed as 1.915, but how is it stored? As 1.9149999999993, or something like that?
In that case the rounding is correct.
This can happen with real values because of its natural inaccurancy.

Better use the Currency datatype in these cases.

 

[The_Doc_Man]

There is a difference between raw, ROUND, and FORMAT that applies here.

If your value was, as Imb suggested, 1.9149999993, you might expect certain issues that differed between the two. If your formatting was based on a template such as "###.###" then you would see 1.915 for the value for 3 decimal places. But you would probably see 1.914 for ROUND to 3 places. I know that 1.915 doesn't come out even as a binary fraction, so there will be some truncation artifacts. In general, when you have a field of type SINGLE or DOUBLE and the value includes a few decimal places, if you show the "raw" field you will get a fraction like 1.9149999993, but you will see other values if you FORMAT or ROUND the number.

By any chance, is the field that eventually displays this number set for 3 decimal places? It's a property setting so should be relatively easy to find.

 

[arnelgp]

maybe create UDF for your banker's rounding and use it in your query in replacement of the Round() function.

' from CoPilot
Function BankersRounding(ByVal num As Double, ByVal numDecimalPlaces As Integer) As Double
    Dim factor As Double
    factor = 10 ^ numDecimalPlaces
    num = num * factor
    
    ' Banker's rounding logic
    If Abs(num - Int(num)) = 0.5 Then
        If Int(num) Mod 2 = 0 Then
            num = Int(num)
        Else
            num = Int(num) + 1
        End If
    Else
        num = Round(num)
    End If
    
    BankersRounding = num / factor
End Function

 

[Cotswold]

My suggestion is only use integer calculations that are stored in your tables. (by integer in Access I refer to Longs)

Do the calcs, multiply by 100, store the integer. If you want to round then add a rounding before you change it to an integer. When you add up integers you can only ever get the same result.
*AMEND*
In fact, I think that you will find that CLng(DecimalNumber) will round your decimal automatically. :
CLng(18.102) results in 1810 and CLng(18.106) results in 1811

Then on the fly insert the decimal point with string handling, on-screen, or to the printer. Adding a column of printed figures that are even a penny out to the total it is unacceptable. If you always use integers, you have no need to check any of your calculations because you will add up the integers and stick in the decimal point in on the total as well.

(Except to EONNEXT the utility company whose calculations on invoices are often as much as 5pence out, which I find irritating. Mind you, just be careful that EON doesn't slip in an odd £10 to £20 extra in a year by sleight of hand. Not a bad earner with millions of customers. )

 

[DickyP]

This is exactly why the Currency and Decimal Datatypes were invented. (Albeit using data stored as Decimal in a table takes a little manipulating in code to ensure the Variant datatype being used is formatted as decimal - eg, by using Cdec(...) etc.)

 

[bushwacka666]

Hi all. Thanks to everyone for their input thus far. I won't lie: some of it goes over my head at the moment (still pretty new to Access coding as a whole), but I will persevere.

So this is being done in a select query behind a report. I can certainly post the entire query if you'd like, although there are a lot of tables & fields and most of them are irrelevant to this specific issue. To narrow it down to just the relevant fields in question, the query would simply be this:

SELECT [Order Details].ODPrice, [Sales Ledger].SLDisc, [ODPrice]/100*[SLDisc] AS TOTAL, Round([TOTAL],2) AS TEST
FROM (([Sales Ledger Transactions] INNER JOIN [Sales Ledger] ON [Sales Ledger Transactions].STSLMN = [Sales Ledger].SLMN) LEFT JOIN Orders ON [Sales Ledger Transactions].STMN = Orders.ORSTMN) LEFT JOIN [Order Details] ON Orders.ORMN = [Order Details].ODORMN

ODPrice is the price of an item, SLDisc is a % discount applied to a customer. So TOTAL would be a simple calculation of how much ODPrice is discounted by. For the specific example I mentioned in the first post, ODPrice = 19.15 and SLDisc = 10.00. So TOTAL calculates as 19.15/100*10.00 = 1.915. Logically you might think, "well wouldn't we just round that up to 1.92 regardless?" but unfortunately that's not how this thing has to work.

For reference, ODPrice and SLDisc both have their data types set as Currency, and decimal places set to Auto. I did double-check to see if the "true" values in the fields were in fact longer but, no, it seems that what I see is what I've got. So whilst plog's suggestion sounded plausible, I honestly don't think there's any rounding going on beforehand. Even when creating a fresh test query stripped down to just the relevant fields in question, I'm still seeing the same behaviour.

*

EDIT: There could still be some merit to the theory though. I've just tried a test query where I directly entered a couple of figures as an expression and then went to total those:

1.915 AS ThisShouldGoUp, 3.165 AS ThisShouldGoDown, Round([ThisShouldGoUp],2) AS IsItUp, Round([ThisShouldGoDown],2) AS IsItDown

Sure enough, 1.915 went up to 1.92 and 3.165 went down to 3.16. So it seems banker's rounding does work on expressions, but I still can't see what would be different about the figures when this program is calculating them.

 

[CJ_London]

What about using
Round ([ODPrice]/100*[SLDisc] ,2) AS TOTAL

 

[bushwacka666]

What about using
Round ([ODPrice]/100*[SLDisc] ,2) AS TOTAL

No difference, I'm afraid. Still makes all the figures round down, even when you'd expect them to go up instead.

 

[Isaac]

I would just about never use currency in sql server (unless forced) , I've read too many articles about its gotches.
However, this might be your best bet here. And I'm betting if you experimented by converting that Total to several other datatypes, you'd see that its real value under the hood is such that the rounding occurring makes sense.

 

[Imb]

Sure enough, 1.915 went up to 1.92 and 3.165 went down to 3.16. So it seems banker's rounding does work on expressions, but I still can't see what would be different about the figures when this program is calculating them.

Hi bushwacka666,

Banker's rounding does work, but only on exact 1.915, and not when it is a very, very little less then 1.915. And this is caused by the division in 19.15 / 100 * 10.
See the results of:
result = Round(1.915, 2)
result = Round(19.15 / 100 * 10, 2)
result = Round(CCur(19.15 / 100 * 10), 2)

These are respective: 1.92, 1.91, and 1.92.
The conversion with CCur gives an exact result in 4 decimals, that is 1.9150. And this is Banker's rounded to 1.92

Imb.

Addition:

A double rounding would also help:
result = Round(Round(19.15 / 100 * 10, 4), 2)

 

[bushwacka666]

I would just about never use currency in sql server (unless forced) , I've read too many articles about its gotches.
However, this might be your best bet here. And I'm betting if you experimented by converting that Total to several other datatypes, you'd see that its real value under the hood is such that the rounding occurring makes sense.

Isaac, you may have hit the nail on the head here! Alas, I don't have the ability/authorisation to change the data type on the existing ODPrice field. But what I have managed to do in the latest test is this:

• Create two new fields in the Order Details table: ODPriceTN (with data type Number) and ODPriceTC (data type Currency.)
• Entered 19.15 as the value in both.
• Added TOTAL expressions for each one into my test query: [ODPriceTN]/100*[SLDisc] AS NDisc and [ODPriceTC]/100*[SLDisc] AS CDisc
• Rounded each of these, with Round([NDisc],2) AS NTest and Round ([CDisc],2) AS CTest

The N(umber) and C(urrency) fields/results all look like a perfect match for the ODPrice and Disc/TOTAL fields... BUT what do you know, the NTest and CTest values are rounding differently - it looks like rounding is behaving as expected in NTest but constantly rounding down in CTest. Here's a quick screengrab with a few other test values just to help visualise it:

To be honest, this makes no sense to me! You would think the term "banker's rounding" would imply that its intent was to be used with currency, yet it doesn't appear to function correctly under the Currency data type? Baffling...

 

[The_Doc_Man]

Note that currency is an "exact" data type because it is implemented as a LONGLONG (64-bit) integer with automatic scaling. Because it is a scaled integer, I don't know what rounding will do to it, but the fact that it IS a typecast of a non-scientific number means it has different representation rules than SINGLE or DOUBLE data types.

 

[arnelgp]

Note that currency is an "exact" data type because it is implemented as a LONGLONG (64-bit) integer with automatic scaling.

how did you know such thing? i maybe wrong but prior to a2013 there is no LongLong data type.

 

[Josef P.]

Note: Currency

Currency data type

Office VBA reference topic

learn.microsoft.com

=> Currency = Decimal(19, 4) / Numeric(19,4)
+ Info from #14 or

Data type summary

Office VBA reference topic

learn.microsoft.com

Currency (scaled integer) | 8 bytes | -922,337,203,685,477.5808 to 922,337,203,685,477.5807
LongLong (LongLong integer) | 8 bytes | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

 

[Pat Hartman]

Also, unless you actually need to store more than four decimal places for extreme accuracy, you might consider changing your data type to Currency to avoid floating point errors. Don't confuse Currency the data type with Currency the data format. The Currency format, formats the display of your numeric data to include a dollar sign (or pound sign or whatever your native currency uses) but does not impact how the data is stored. The Currency data type which I also use for percents, is a scaled integer and has a fixed number of decimal digits. So, 101.57 is stored as 1015700 with however many leading zeros it takes to fill the fixed length integer. And 5% would be 500 with zeros to the left to fill the fixed length.

 

[The_Doc_Man]

how did you know such thing? i maybe wrong but prior to a2013 there is no LongLong data type.

The older documentation clearly says it is 2 x LONG. I named it according to more modern standards. I agree that the formal data type LONGLONG only is seen rarely and older 32-bit systems wouldn't support it anyway. I've only seen it once on one of my Navy Windows Server systems that was set up for 64-bit operations. However, I can state with absolute certainty that by 2014, the LONGLONG data type was documented. It appears in the VBA Language Specification for v20140424 in Section 2.1 - Data Values and Value Types.

 

[DickyP]

Currency datatype which has certainly been in the JET/ACCESS DB Engine since to 90s and was defined as 8 bytes long in the oldest documentation I have (1998).

The more controllable, but difficult to use JET/ACCESS DB Engine Decimal datatype has been around since at least Access 2010.

The biggest warning is against assuming that VBA and DB engine datatypes are the same - generally yes but variations.

 

[The_Doc_Man]

To amplify DickyP's comment:

When the data type is a purely hardware data type (e.g. INTEGER, DOUBLE) then you can assume that JET and a PC-based DB engine have the same interpretation of the data type.

When the data type is a typecast of another data type, that assumption MIGHT be incorrect.

For example, DATE is actually a typecast of DOUBLE. But a DOUBLE can theoretically hold a wider range of values than a DATE can. The implication is that when it is treated as a date, special date/time formatting routines apply. A DATE can only be formatted correctly if the underlying DOUBLE represents a date from 00:00:00 of 1 Jan 0100 to 23:59:59 of 31-Dec-9999, and the formatting routines will not render fractions of a second in a DATE field even though DOUBLE format has enough precision to store a time down to the microsecond for times up to something after the year 3000.

The Boolean data type could easily fit within a byte, but is actually a typecast of a WORD / INTEGER. VBA standards say the only legal values are 0 (for False) or -1 (for True). Programmatically, when you assign a value to an integer to a Boolean, a process called Let-coercion follows this simple rule, quoted from the VBA Language Specification v20140424 Section 5.5.1.2.2 Let-coercion to and from Boolean: "If the source value is 0, the result is False. Otherwise, the result is True." In other words, that value of -1 for True in Booleans is not true for conversions. Any non-zero value is counted as True, not just -1.