Round function and Single vs Double data types

[DaveBo]

Just an FYI
I was just bitten in the Access VBA by this ... idiosyncrasy,

Note the difference in rounding of the same number when it's Single vs Double.

Public Sub Test_Round()
    Dim sngX As Single
    Dim dblX As Double
 
    sngX = 45.315
    dblX = 45.315
    
    Debug.Print Round(sngX, 2)  ' = 45.31
    Debug.Print Round(dblX, 2)  ' = 45.32
End Sub

I'm sure there's a very good explanation for this, that will go right over my head, like "Haven't you heard of 'Drunken Banker's Rounding?'

It (mis)behaves the same way in Access VBA, VB6 and VB.NET,
so, I'll give MS points for keeping their inconsistency consistent.

[westconn1]

that is really interesting and now i am going to do some tests to see if it will resolve another problen i was having. thiugh i doubt that it will

pete

[DaveBo]

Question, does anyone know if the change from single to double (which fixed the problems that I "knew" about) will actually create other problems?

i.e. does using a double have it's own quirks that will just shift the math problems elsewhere?

Thanks, DaveBo

[westconn1]

double is more accurate than a single, but uses more memory for storage

pete

[Hack]

A Single is an 4 byte floating point number ranging from -2.40E38 to 3.40E+38
A Double is an 8 byte floating point number ranging from -1.79E+308 to 1.79E+308

[moeur]

Why is this a problem?
As you stated, VB6 uses Banker's rounding which is a rule for determining which way to round a 5. It should always make the result even. This is to try to even out the number of times rounded up vs. rounded down.

If instead the rule rounded toward the odd number (as you've demonstrated it does with single precision numbers) the evening out will be the same.

The last digit after rounding is always going to have an uncertainty associated with it and hence should not be heavily depended on.

[szlamany]

First you have to live with Banker's rounding - that's the MS way...

But you chose to use SINGLE and DOUBLE - that was a poor choice.

Use CURRENCY if you want accuracy - single and double are imprecise database - in other words they do not work!

[moeur]

First you have to live with Banker's rounding - that's the MS way...

No you don't, you can always write your own routines if you prefer another method.

single and double are imprecise database - in other words they do not work!

What?...that would be news to millions of programmers around the world who use double precision numbers.

Use CURRENCY if you want accuracy

Currency, like integer and long, work well within certain bounds. Any more than four decimal places and Currency can't handle it.

[MartinLiss]

...What?...that would be news to millions of programmers around the world who use double precision numbers....

It's actually pretty well known that Doubles are imprecise. Try this.

Dim x As Double
    Dim y As Long
    
    x = 5
    For y = 100 To 1 Step -1
        x = x - 0.1
        If x = 0 Then
            MsgBox "this will never happen because doubles are imprecise"
        End If
        Debug.Print x
    Next

[szlamany]

No you don't, you can always write your own routines if you prefer another method.

You certainly can - then get burned when one of the programmers in your shop uses the language default function...

What?...that would be news to millions of programmers around the world who use double precision numbers.

Yes - I've been trying to educate those millions of programmers for 20 years now. Floating point is for launching missles - speed was the reason for it's creation. It uses log-tables for math and does neat little things like store 4 as 3.99999

Currency, like integer and long, work well within certain bounds. Any more than four decimal places and Currency can't handle it.

We develop financial and pension software here - currency is what we are forced to live with in the MS/VB/PC world. Our mainframes had a 64 digits exact datatype for math - they called it string arithmetic.

We have been down this path in other threads before...

http://www.vbforums.com/showthread.p...le+double+evil