values in an exported file

[Guest]

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.

 

[onedaywhen]

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.

Are you sure?

Try changing the Access field type to Number (Double).

If this value is intended to be 0.04 throughout, and I think from the
OP's description it is, then I would recommend the OP changes the data
type to DECIMAL(p, 2).

I have two reasons for this recommendation

1) If the nature of the data is reality is 'numeric two fixed decimal
places' then the OP should model it as such; inexact data types
introduce a risk, from which the OP has already been bitten.

2) If the OP already has data that is REAL (Single) in nature then
casting it to FLOAT (Double) will not improve the situation, whereas I
think DECIMAL will i.e.

CSNG(0.0399999991059303) will be coerced to DECIMAL 0.04 and remain as
0.04 when coerced to FLOAT (Double) on export to Excel

whereas

CSNG(0.0399999991059303) will remain unchanged when cast/coerced as
FLOAT (Double) in Access and Excel and the problem remains.

Excel uses Doubles for all numeric data

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.

Jamie.

--

 

[Guest]

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?

 

[onedaywhen]

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?

I mean the latter i.e. an in-cell fixed point type.

In MS Jet terms, the Excel data types are CURRENCY, DATETIME, LOGICAL
(yes/no), FLOAT (Double), MEMO and NVARCHAR(255).

For an Access man such as yourself, I know of no better way to create a
CURRENCY column than this:

CREATE TABLE
[Excel 8.0;DATABASE=C:\DropMe.xls;].TestExcel (
cur_col CURRENCY,
float_col FLOAT
);

As usual, here's some VBA to create a test database (i.e. Excel) and
demonstrate that the type is indeed CURRENCY in nature:

Sub ExcelCurrency()
Dim con
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
.Open

' Create test 'database' (new workbook)
' and 'table' (new sheet and Defined Name)
.Execute _
"CREATE TABLE" & _
" [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
".TestExcel" & _
" (cur_col CURRENCY, float_col FLOAT);"

' Create test data (Banker's rounding
' should round 0.00025 to 0.0002)
.Execute _
"INSERT INTO [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
".TestExcel (cur_col, float_col)" & _
" VALUES (0.00025, 0.00025);"

' Create test data (Banker's rounding
' should round 0.00035 to 0.0004)
.Execute _
"INSERT INTO [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
".TestExcel (cur_col, float_col)" & _
" VALUES (0.00035, 0.00035);"

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

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

End With
End Sub

Jamie.

--

 

[onedaywhen]

John Nurick wrote:
In MS Jet terms, the Excel data types are CURRENCY, DATETIME, LOGICAL
(yes/no), FLOAT (Double), MEMO and NVARCHAR(255).

This is the closest I could find to any details on MSDN:

http://msdn.microsoft.com/library/d...dbc/htm/odbcjetmicrosoft_excel_data_types.asp

No mention of MEMO but it can be explicitly created e.g.

Sub ExcelMemo()
Dim con
Set con = CreateObject("ADODB.Connection")
With con
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
.Open

.Execute _
"CREATE TABLE" & _
" [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
".TestExcel2" & _
" (text_col NVARCHAR(255), memo_col MEMO);"

' This will succeed, showing the MEMO
' col can accommodate 300 characters
.Execute _
"INSERT INTO " & _
" [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
".TestExcel2 (text_col, memo_col)" & _
" VALUES (STRING$(255, 'x')," & _
" STRING$(300, 'x'));"

' This will succeed, showing the NVARCHAR
' col cannot accommodate 300 characters
.Execute _
"INSERT INTO " & _
" [Excel 8.0;DATABASE=C:\DropMe.xls;]" & _
".TestExcel2 (text_col, memo_col)" & _
" VALUES (STRING$(300, 'x')," & _
" STRING$(300, 'x'));"

.Close
End With
End Sub

 

[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');"

 

[onedaywhen]

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.

When you use a CREATE TABLE in SQL DDL where the target workbook does
not exist, a new workbook will be created containing one worksheet and
a Defined Name ('named range') limited to the rows created. If you only
executed the CREATE TABLE and no INSERT INTO statements, the extent of
the Excel 'table' will limited to the column headers. Therefore, when
you typed data into the 'first two blank rows' I'm guessing they were
outside of the Excel 'table'.

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):

Jet must allow for uncontrolled editing in the Excel interface and it
does it using the data itself and TypeGuessRows. I'm guessing you are
already aware of this
(http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/)
so I won't elaborate.
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

Jamie.

--

 

[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".

 

[onedaywhen]

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

Hmm, I think this is about perception.

I can create an Excel column of numeric values (double float), even set
Data Validation for the column to 'Decimal', yet I can still paste a
300 character text value into that column. Would you thereby conclude
that Excel does not have an in-cell numeric type?

In Excel it holds true that if you circumvent the functionality that
provides data typing (cell format, Data Validation, Jet interface, etc)
then you have no data typing.

To employ a Jet analogy, if date/time values are persisted as FLOAT
(Double) does this mean Jet has no distinct DATETIME data type?

Again I make the point that weak data typing is a feature of Excel i.e.
people want, expect and like it that way. Excel weakly types the
column/cell and tries to 'format' the value supplied according to that
type. However, unlike, say Jet with an .mdb file which throws an
exception when the data cannot be coerced to the type, Excel will
effectively change the data type of the column to fit the data. In
fact, I think it is true that of the Excel interface that values
inserted into cells are not coerced at all (loss of accuracy associated
with double float, cell size limit and such aside).

I don't want to argue. If your perception is that Excel has no currency
type then based on your criteria (strongly typed, does not change type
based on data supplied) then you are right. This is not a "let's agree
to differ" stance, rather a "we're looking at things differently" one.

Jamie.

--