Linked Table Data Types or Field Formats

[crazedNdumbfuzed]

Can anyone help me figure out how to make sure that linked tables (from xls)
import in the correct format or data type? I have a field which in 4 out of
5 of my linked tables comes in as text, and in the 5th linked table will only
come in as "number" type

Since I'm not allowed to change the data type in a linked table within
Access, I have tried making sure that the columns in excel are formatted
exactly as I would like in Access, and refreshed all linked data, but this
doesn't help.

I have an extremely complex database which by the single "type mismatch"
error this is causing... is now totally unusable! I'm almost ready to forget
using the extremely productive "linked" tables, and go back to manually
pasting data

help!?

 

[strive4peace]

Access interpreets data type based on the first 20-50 rows of the data
in the Excel Spreadsheet ... it helps to make the first row a 'dummy'
row that simply sets up the data type... if that is not feasible, you
might try:

1. prefacing the numbers (that are meant to be text) with a single quote
' in Excel -- the quote mark won't show but it specifies that cell is
text and should align on the left ... but Access may ignore it

or

2. Format the cells as 'text' in Excel

I haven't actually tested these two methods, but it is worth a try -- I
always use a 'dummy' row and make sure it contains TEXT for each column
that should be text data type, date if the data type should be a date,
and 0 if the data type should be numeric ...

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*