change number field to text field

[Guest]

importing an excel workbook from one of our clients. the field in question
should be text but was described as numeric by client. so when i import the
info, any alpha numeric info in that field rejects the record because of type
conversion

how do i import the workbook using vb and change the field in question to
text, so i can import into an access table?

thanks for your help

 

[Bob Quintal]

importing an excel workbook from one of our clients. the field in
question should be text but was described as numeric by client. so
when i import the info, any alpha numeric info in that field
rejects the record because of type conversion

how do i import the workbook using vb and change the field in
question to text, so i can import into an access table?

thanks for your help

Open the table in design view, change the field's type from number
to text, set the length of the changed field to the number of
characters required.

You don't specify how the vb code defines the data, you may also
need to change the field definition in the import specification. if
used.

 

[Guest]

thanks, but this is similar to get external data. it is the excel book that
needs fixing before i import. the user identified the excel field as
numeric, but then included alphanumeric information, as well as numeric
information for the records.

so when i hit the alphanumeric when importing, i then get the file
conversion problem, and all those records reject.

so somehow i need to fix the excel book before i execute the
transferspreadsheet

 

[Bob Quintal]

thanks, but this is similar to get external data. it is the excel
book that needs fixing before i import. the user identified the
excel field as numeric, but then included alphanumeric
information, as well as numeric information for the records.

so when i hit the alphanumeric when importing, i then get the file
conversion problem, and all those records reject.

so somehow i need to fix the excel book before i execute the
transferspreadsheet

cells in Excel do not have a type definition, cannot be defined as
numeric or text, therefore there is nothing to fix in Excel.

Open the table in design view, change the field's type from
number to text, set the length of the changed field to the number
of characters required.

You don't specify how the vb code defines the data, you may also
need to change the field definition in the import specification.
if used.

 

[Guest]

then why do i get type conversion errors, when i try the option get external
data and import the excel book? at this point, it is creating its own access
table, i am merely pulling in the excel book.

thanks for your help

thanks, but this is similar to get external data. it is the excel
book that needs fixing before i import. the user identified the
excel field as numeric, but then included alphanumeric
information, as well as numeric information for the records.

so when i hit the alphanumeric when importing, i then get the file
conversion problem, and all those records reject.

so somehow i need to fix the excel book before i execute the
transferspreadsheet

cells in Excel do not have a type definition, cannot be defined as
numeric or text, therefore there is nothing to fix in Excel.

 

[rquintal]

then why do i get type conversion errors, when i try the option get external
data and import the excel book? at this point, it is creating its own access
table, i am merely pulling in the excel book.

thanks for your help

Access makes a best guess in defining the field types for its table,
by looking at the first few lines of the spreadsheet. If the first few
lines are numeric, then you get a numeric field in Access. even if
later rows are text.

Import the table, then modify the structure to make the bad columns
text.
The $ImportErrors table will tell you which ones.
Now delete the data from the rows of that table, and reimport, except
choose the existing table in the wizard.