Data Returned Has Rounding Issues

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

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
 
damarsh said:
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?
Click to expand...


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.
 
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?
 
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.
 
Got it, thanks for the help

Marshall Barton said:
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?
Click to expand...
Click to expand...
 
Back
Top