Inconsistent rounding using Format

I ran into a very simple rounding error with Format. It seems that rounding is inconsistent depending on the value.
This occurs when formatting any variable types - double, currency, etc - even literals.

Examples using f= “#.00”:

Format(17.655, f) = 17.66
Format(217.655, f) = 217.66 -> ok
Format(517.655, f) = 517.65 -> one cent difference

Obviously this makes a proper column of arithmetic impossible.

Anybody solved this?

quite simple, once you know about and understand the intraciys of the DOUBLE

add a few more Zeros on your format and you will see.

		dim d1 as double=17.66
		dim d2 as double=217.655
		dim d3 as double=517.655
		dim f as string="#.00000000000000"
		msgbox format(d1,f)+"/"+format(d2,f)+"/"+format(d3,f)



517.655 is not represented at 100% accuracy (I won’t go into the why, there are plenty of documents that discuss this)
but as such, it rounds correctly, but not … :slight_smile:

I’d suggest using CURRENCY datatype for “Double” values with less that 4 digits to the right of the decimal
EDIT : you’re right… even CURRENCY screwed up…and it shouldn’t have as it uses LONGINT (or so I thought)

I do use Currency. Thats why I posted this. I thought Currency was supposed to fix this issue. I guess not.

Try this:

The issue is that format converts you currency to double to perform its task. Which introduces typical double idiosyncrasies.

I looked at the ToText method in the new framework, but it does not do the same as format.

The only available solution seems be to write a rounding routine before you feed to format.

Although Rick’s Money class may do what you need.