values in an exported file

[Patrick]

Hey!

I've exported a file from access using transferspreadsheet. One field in
access which has a data type of "Number" with a field size of "Single" is
populated by an update query to be 0.04. When I export the spreadsheet to
excel the value becomes: 0.0399999991059303 which rounds to .04 in excel
but when I use this percentage against large numbers and then sum them I have
issues.

I must be missing something obvious.

Thank you!

Patrick

 

[John Nurick]

This happens because the value 0.04 can't be stored exactly as a binary
number (just as many values - 1/3 for example) can't be stored exactly
as a decimal number. Excel uses Doubles for all numeric data, and the
extra precision of Double over Single is exposing the issue.

Try changing the Access field type to Number (Double). That way, you
have the same precision in both places.

 

[Patrick]

Thank you!

This happens because the value 0.04 can't be stored exactly as a binary
number (just as many values - 1/3 for example) can't be stored exactly
as a decimal number. Excel uses Doubles for all numeric data, and the
extra precision of Double over Single is exposing the issue.

Try changing the Access field type to Number (Double). That way, you
have the same precision in both places.

 

[John Nurick]

Actually, Excel's CURRENCY is a numeric type that is fixed point (i.e.
not a floating point) in nature, with a fixed scale (decimal places) of
four.

Do you mean VBA's Currency data type, or some way (new to me) of forcing
Excel to store numeric values in cells in a fixed-point format? If the
latter, how does one control it?

 

[John Nurick]

Hi Jamie,

.Execute _
"INSERT INTO [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
".TestExcel (cur_col, float_col)" & _
" VALUES (0.00025, 0.00025);"

Having installed Office 2003 SP2 I can't do this sort of thing any more.
I hacked your sub so it executed only the CREATE TABLE statement (adding
a TEXT column for the hell of it), and then opened the resulting .xls
file and typed the 0.00025 and 0.00035 into the first two blank rows.

The values in cur_col were not rounded, and the values in text_col were
recognised as numbers, so it seems that - as I thought - even though the
Excel driver has a concept of column types, Excel itself doesn't.

Going a step further, I re-hacked your code to connect to the worksheet
and display the data (using $ on the sheet name to get the UsedRange
because I hadn't updated the "TextExcel" named range when I typed in the
data):

Dim rs
Set rs = .Execute( _
"SELECT cur_col, float_col, text_col FROM" & _
" [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
".[TestExcel$];")

MsgBox _
"cur_col" & vbTab & "float_col" & vbTab & "text_col" & vbCr & _
rs.GetString
rs.Close

Again, the values in cur_col were not rounded.

So I surmise that the situation is this:
1) It's possible to use the Excel driver to execute a CREATE TABLE
statement that specifies column types.

2) Doing so does nothing to restrict the kinds of data that can be
inserted into the columns via the Excel UI or VBA.

3) But provided you don't drop the connection and aren't using an
up-to-date installation of Access 2002 or 2003, the Excel driver will
coerce values you inserte or update by executing SQL statements.

Here's a couple of things I'd try if I had an older machine here:

a) modify your test sub to execute this:

"INSERT INTO [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
".TestExcel (cur_col, float_col)" & _
" VALUES (12345679012345.1234, 12345679012345.1234);"

Then dive into VBA and check the Formula and Value properties of the
cells. If the CREATE TABLE created a currency column, the cur_col value
will have all 19 digits, while float_col will lose the last three or so.

b) close the connection, then re-connect and try to execute something
like this

"INSERT INTO [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
".TestExcel (cur_col, float_col)" & _
" VALUES ('This is a numeric value', 'Me too');"

 

[John Nurick]

Looking your approach more generally, I think you may be expecting too
much <g>. It is a 'feature' of Excel that it does not have strong data
typing. Using Jet to manipulate Excel data can provide a fair degree of
data typing. However, opening the Excel workbook in Excel rightly frees
it of the Jet data typing.

Apologies if I did get your hopes up

I'll take this as accepting that Excel doesn't have "an in-cell fixed
point type".