G
Guest
I have data in an excel spreadsheet that contains text and numbers - each
data type is in a separate column. Eg:
A B C D E
1 Ticker Shares Sector Price Value
2 IBM 100,000 Technology $83.21 $8,321,000
When I create a linked table in Access 2003 using this worksheet, the Share
column converts into a number data type, but the Price and Value columns
convert to the default Text data type in Access.
When I open the linked table in Access the first time, all the data is
represented correctly. However, when I run a query to convert the text
"values" to values (I've tried val([Price]), ccur([Price]), cdbl([Price]),
etc), the resulting query fields show either #NUM! or #ERROR!
To add insult to injury, when I now open the linked table (i.e. after having
run the query), it too shows #NUM! or #ERROR! where it used to show Price and
Value. To keep it frustrating, the Shares field is linked as a number, and
stays as a number - which is what I'm trying to get the Price and Value
fields to do, too.
I have tried formatting the entire column in Excel, e.g. D
(i.e., not just
the column of cells beneath the header), as numbers, as currency, as whatever
numerical format, it makes no difference. The shares column will be linked
as number data type, and all the others as text.
The data in Excel is usually hundreds of rows that have been downloaded from
a financial data web site, as an excel spreadsheet, so it's likely that the
columns are "pre-formatted" by the vendor of that site - but in Excel, they
function perfectly well as numbers - they can be multiplied, added, etc, and
they format as numbers perfectly well.
I know that I can import the worksheet as a table and then manually (or via
code), change the field's data type. But I'd rather not do that (for now) -
as it will reduce the efficiency of the process I've set up. I'd also rather
not link the worksheet without the headers (which may be screwing it up,
somehow), but I rely on this to create the field names (rather than field1,
field2). As you may have guessed, this linked table, which is the latest data
I need, is then appended, using an append query to my main Access data table.
Any ideas how to handle this problem so that all the excel columns that
contain numbers which stay as numbers? The link table wizard (and the import
wizard) have the data type option greyed out, otherwise, I wouldn't be
writing this.
Many thanks.
data type is in a separate column. Eg:
A B C D E
1 Ticker Shares Sector Price Value
2 IBM 100,000 Technology $83.21 $8,321,000
When I create a linked table in Access 2003 using this worksheet, the Share
column converts into a number data type, but the Price and Value columns
convert to the default Text data type in Access.
When I open the linked table in Access the first time, all the data is
represented correctly. However, when I run a query to convert the text
"values" to values (I've tried val([Price]), ccur([Price]), cdbl([Price]),
etc), the resulting query fields show either #NUM! or #ERROR!
To add insult to injury, when I now open the linked table (i.e. after having
run the query), it too shows #NUM! or #ERROR! where it used to show Price and
Value. To keep it frustrating, the Shares field is linked as a number, and
stays as a number - which is what I'm trying to get the Price and Value
fields to do, too.
I have tried formatting the entire column in Excel, e.g. D
(i.e., not justthe column of cells beneath the header), as numbers, as currency, as whatever
numerical format, it makes no difference. The shares column will be linked
as number data type, and all the others as text.
The data in Excel is usually hundreds of rows that have been downloaded from
a financial data web site, as an excel spreadsheet, so it's likely that the
columns are "pre-formatted" by the vendor of that site - but in Excel, they
function perfectly well as numbers - they can be multiplied, added, etc, and
they format as numbers perfectly well.
I know that I can import the worksheet as a table and then manually (or via
code), change the field's data type. But I'd rather not do that (for now) -
as it will reduce the efficiency of the process I've set up. I'd also rather
not link the worksheet without the headers (which may be screwing it up,
somehow), but I rely on this to create the field names (rather than field1,
field2). As you may have guessed, this linked table, which is the latest data
I need, is then appended, using an append query to my main Access data table.
Any ideas how to handle this problem so that all the excel columns that
contain numbers which stay as numbers? The link table wizard (and the import
wizard) have the data type option greyed out, otherwise, I wouldn't be
writing this.
Many thanks.