Excel spread sheet linked via Access question

[Guest]

Hi, I've seeked assistace regarding excel and how I could limit what is
entered into a cell. Now I've done a Validation and it works. Now that I
have done this and
I've linked my spreadsheets to MS Access and another question comes up. See,
I failed to recoginize that not all account #'s (data being used) truly isnt
SSN's it may consist of Alpha and numeric characters. The probem isn't with
excel as I set it up as text in my Validation. But there is a probem with
Access. When the info is
carried over (table is linked to Access) the info reads as numeric instead of
text. I get and error if a Alpha character exists and or if the account #
starts with zeros they are droped from the account # detail. I need all
detail to show for reporting purposes. What can I do. I've tried using an
input mask as "000000000", but that didn't work. Any assistance would be
great.

 

[John Vinson]

Hi, I've seeked assistace regarding excel and how I could limit what is
entered into a cell. Now I've done a Validation and it works. Now that I
have done this and
I've linked my spreadsheets to MS Access and another question comes up. See,
I failed to recoginize that not all account #'s (data being used) truly isnt
SSN's it may consist of Alpha and numeric characters. The probem isn't with
excel as I set it up as text in my Validation. But there is a probem with
Access. When the info is
carried over (table is linked to Access) the info reads as numeric instead of
text. I get and error if a Alpha character exists and or if the account #
starts with zeros they are droped from the account # detail. I need all
detail to show for reporting purposes. What can I do. I've tried using an
input mask as "000000000", but that didn't work. Any assistance would be
great.

Excel doesn't have "strong" datatypes; the format or validation of a
cell does not explicitly mark that cell as being of Text datatype.
Access must look at the first few rows of a linked spreadsheet and
guess at the datatype. If the first ten rows, say, have all numeric
characters in this field, Access will guess (incorrectly) that the
datatype is Number.

One getaround is to put a "dummy" row at the top of the sheet with an
indubitable text value ('X say, or 'This Is Text Dummy!') to force
Access to define this field as text.

John W. Vinson[MVP]

 

[Guest]

Good thought and very tricky! Thanks I will try that. It funny how some
time it the obvious one doen't think about! Thanks again.