Exporting to Excel, decimals change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I exported some data from an Acces97-query to an Ecel97-sheet. In the
excell-sheet the figures are changed. For example, a field that containes
1503.6 (not a calculated field) in Access, now shows in Excell
1503,59997558593. What is the cause of this and, more important, waht is the
solution for this problem ?

Thanks in advance.
 
This will happen if the Access field is of type Number (Single). The
limited precision of the Single type means the closest it can get to
1503.6 is about 1503.59997558593. Excel stores all numbers as Doubles,
and the conversion from one type to the other exposes the true value.
This VBA code illustrates the problem:

Sub Test()
Dim S As Single
Dim D1 As Double, D2 As Double

S = 1503.6
D1 = S
D2 = 1503.6

If D1 = D2 Then
MsgBox "No difference"
Else
MsgBox "1503.6 <> 1503.6!"
End If

End Sub

The solution (or a reasonable approximation to one) is to change the
Access field to Number (Double).
 
Back
Top