Value transfered to cell differs from value in record set

[Tony29]

I use ADODB.Connection and ADODB.Recordset to pick up data from an Acess
database and transfer that data to a range on my spreadsheet. 99.999% of the
time I have perfect success with this which gives me some confidence that I'm
using the technique correctly (yeah ... right!).

I have found that a value retrieved from the database (the value is 0.002
and comes from a query result field defined as Standard, 3 decimal) is
transfered to the relevant cell on the spreadsheet as 0.000 (zero). The
target cell is defined as numeric, 3 decimal places and this does not change
before or after the transfer. Values from other rows of the same query
result field, and even values of other query result fields, also defined as
Standard, 3 decimals, transfer cleanly and correctly to their relvent target
cells.

I prepare the complete target area with ..Range(...).ClearContents to
preserve formatting.

I have executed the code in step and watch modes to verify that the value in
the record set just prior to the transfer is 0.002 ...

The transfer is achieved with .. .Cells(r, c + f).Formula = rs.Fields(f).Value

OS = Win Vista Ultimate
Excel 2003

Any help please? Thanks in advance

 

[Smallweed]

Strange - does ... .Cells(r, c + f).Value= rs.Fields(f).Value make any
difference?

 

[Tony29]

No - it doesn't make a difference. I had already tried it with ".Value" and
with no qualifier - same result.

However, your suggestion made me look at it again. I set a watch on the
target cell with Sheets("Sheet2").Range("R27") notation and then looked thru
the properties of the cell - maybe I missed something, but there is NO
".Value" property on a cell!!!??? There is a ".Value2" so I tried ...
..Cells(r, c + f).Value2= rs.Fields(f).Value and this works perfectly for all
target cells of all data types.

So, I guess in one respect the problem is solved ... but in another respect,
does Microsoft have something to explain about the inconsistent result
achieved using ... .Cells(r, c + f).Value= rs.Fields(f).Value

Thanks for your interest.