Converting format fields after importing from Excel

  • Thread starter Thread starter Aline
  • Start date Start date
A

Aline

Hello!

I imported a sheet from Excel and I've added datas into a
table, using TransferSheet command for transfer, and a
query for add data.
In Excel the format is Text for all fields, but in the
table I have different formats for each field (number and
currency).

In Access 2000 it works fine, data is converted without
errors, but in XP is not so easy. I receive errors for
each field in other format that

I don't have any idea how to work this out.

I'm thinking to convert each fiels, by VBAcode but I don't
know how.

I welcome any solution,
Aline
 
I usually import into a staging table (all data types may be text) then use
an append query and format commands to move the data to the right table with
the right type.

For a one time import, I just import to a New table.
Then review the data and use append queries.
=======================================

Since Excel is not a database, using "mixed" data types in a column causes a
problem when Access has to "guess" which one to use. Access examines the
first 15 rows (or so) and makes a guess. If you want to "force" Access to
guess a text data type then make the first entry in that column a text
value. The reason you get errors is that the first 15 rows are all numeric
and then the text data shows up after Access has already guessed the data
type to be Numeric.

The way to make a text value in your data of numbers is to include a
character!
e.g. 123A


==================================
 
Back
Top