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)returns
17.660000000
217.655000000
517.654999999
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 … 
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.
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.