Change data type

[NTXCoog]

I'm importing an Excel spreadsheet into Access using
DoCmd.TransferSpreadsheet. One of the fields is coming in
as a number field, but I'd like it to be a text field.
Can I force it to import as text? If not, how can I
change the field from number to text in my code?

Thanks in advance.

 

[John]

If you are appending your data into a table, change the
field, in this table to text.

 

[Ken Snell]

You can try adding an extra row at the top of the spreadsheet and be sure
that nonnumeric characters are in the column that you want to be treated as
text. However, ACCESS may ignore that and still bring the field in as a
number, as it looks at the first series of rows and decides what to use.

If that doesn't work, you can preface each number in the column in the
spreadsheet with a ' character; that'll tell ACCESS to see the number as
text.

Or, you can import the spreadsheet into a temporary table, and then use an
append query to copy the data, in the correct format (using calculated
fields that wrap the field's value with a Format function that tells ACCESS
to use a text format), into the permanent table.

 

[NTXCoog]

That last method worked for me. I didn't want to require
a change in the Excel file.

I was just hoping there'd be something easy. It's so easy
to do in design view of the table that I thought it might
be as easy in code.