How do I specify data types within Access for linked spreadsheets? For
some reason, Access keeps tagging my Excel spreadsheet’s zipcode
column as a number. It is actually a text field (some of the zipcodes
contain zip+4 = “92119-3823”).
I’ve tried everything I can think of, but STUPID STUPID Access keeps
seeing that the majority of rows in the column look like normal
numbers (most don’t have zip+4), so it specifies that the linked
column is numeric. Then when I use it, it gives me all kinds of errors
because it contains clearly non-numeric data.
I want to find some way to tell Access: this column is TEXT YOU IDIOT.
Whatever you think you STUPID PROGRAM, just leave it as TEXT!!!!
Yes, I’m frustrated. :-) |
Request for Question Clarification by
mathtalk-ga
on
15 Dec 2002 11:46 PST
Hi, vaughan-ga:
Please advise us what version of Access and what version of Excel you
are working with. Also, confirm that you have set the formatting of
the entire column in Excel to text (Excel allows the formatting
properties to vary on a cell by cell basis, where in treating a linked
worksheet as a relational table you expect all entries within a column
to have the same format/datatype).
regards, mathtalk-ga
|
Clarification of Question by
vaughn-ga
on
15 Dec 2002 13:43 PST
Excel 2000 9.0.3821 SR-1
Access 2000 9.0.3821 SR-1
Yes, the entire column is formatted text.
|
Clarification of Question by
vaughn-ga
on
15 Dec 2002 13:52 PST
Isn't there some way to SPECIFY within Access the data type for linked
columns instead of relying upon Access to guess? How can you have any
control with spreadsheets formatted as "general" that have info that
'looks' like a number but is actually text?
Case in point, postal codes. Maybe MOST of the time they only have
numeric data, but sometimes you get a Canadian order with an
alpha-numeric postal code (or zip+4). In your Access database, all of
the internal tables that relate to the linked spreadsheet have to KNOW
what data type they are. They can't have it change unexpectedly as the
linked data gets changed.
I'm just hoping, thinking that there MUST be a solution to this.
|
