Data Returned Has Rounding Issues

[Guest]

Hello. I am having problems with a database query in Excel. I am using the
code given below to input data into a database using VBA. I am storing a
number given with two decimal places (55.55) in Access as a 'Number' Data
Type with 'Single' Field Size. I am then extracting the relevant data using
Microsoft Query from a database, but my returned values have more decimal
places (55.54999). Am I doing something wrong with inputting the values into
Access or Querying the data from Access?

'Create New Record
.AddNew
.Fields("100") = Range("H" & r).Value
.Fields("200") = Range("I" & r).Value
.Fields("300") = Range("J" & r).Value
.Fields("400") = Range("K" & r).Value
.Fields("500") = Range("L" & r).Value
.Fields("600") = Range("M" & r).Value
.Fields("700") = Range("N" & r).Value
.Fields("800") = Range("O" & r).Value
.Fields("900") = Range("P" & r).Value
.Fields("1000") = Range("Q" & r).Value
.Fields("1100") = Range("R" & r).Value
.Fields("1200") = Range("S" & r).Value
.Fields("1300") = Range("T" & r).Value
.Fields("1400") = Range("U" & r).Value
.Fields("1500") = Range("V" & r).Value
.Fields("1600") = Range("W" & r).Value
.Fields("1700") = Range("X" & r).Value
.Fields("1800") = Range("Y" & r).Value
.Fields("1900") = Range("Z" & r).Value
.Fields("2000") = Range("AA" & r).Value
.Fields("2100") = Range("AB" & r).Value
.Fields("2200") = Range("AC" & r).Value
.Fields("2300") = Range("AD" & r).Value
.Fields("2400") = Range("AE" & r).Value
.Update

 

[Marshall Barton]

Hello. I am having problems with a database query in Excel. I am using the
code given below to input data into a database using VBA. I am storing a
number given with two decimal places (55.55) in Access as a 'Number' Data
Type with 'Single' Field Size. I am then extracting the relevant data using
Microsoft Query from a database, but my returned values have more decimal
places (55.54999). Am I doing something wrong with inputting the values into
Access or Querying the data from Access?

That's typical of floating point numbers because many
numbers can not be converted from what you type to floating
point with exact precision. Think of writing doen the
decimal number for 1/3.

Try using a fixed point number type such as Currency instead
of Single.

 

[Guest]

In the Field Size, I do not see a number type that will allow this fixed
point number. Either the Field Size does not include decimals, or goes to 8
decimal places. There is nothing in between. Is there an update to Office
2003 that will allow this fixed point number?

 

[Marshall Barton]

Sorry, if I misled you. Fixed point is only a way to store
and calculate values, it is not an Access term. I was
trying to tell you to set the field's **Data Type** to
Currency.

 

[Guest]

Got it, thanks for the help

Sorry, if I misled you. Fixed point is only a way to store
and calculate values, it is not an Access term. I was
trying to tell you to set the field's **Data Type** to
Currency.
--
Marsh
MVP [MS Access]

In the Field Size, I do not see a number type that will allow this fixed
point number. Either the Field Size does not include decimals, or goes to 8
decimal places. There is nothing in between. Is there an update to Office
2003 that will allow this fixed point number?