access excel number text problems

[Scott]

This question is for Access and Excel XP (2002). I have an Excel Spreadsheet
(database) that I linked to Access for DB and updating features. My unique
identifier (Key Field) is a Serial Number (SN). The SN's contain 13 digits,
and in some instances digits and a (one) letter i.e.; 99913R218849. When
Linking the Data from Excel to Access, Access identifies all SN as text only
and attaches a ' to the beginning of each SN'd item that contains digits only
(no letter). Inturn making all SN's (with digits only) TEXT realted. This
creates problems when using the Vlookup function in Excel. For example; by
typing in a SN containing digits only no value can be retuned unless you add
the ' to the beginning of the SN. If I remove the ‘ from the SN the Access
DB in return will not show the SN. I really don't want to break the key
field into seperate fields. I have searched MS web site for answers and tried
several methods to correct this problem with no luck. Can anybody help?
Thank You!

 

[John Nurick]

Hi Scott,

I think it will be possible to solve this one by tweaking some registry
settings. See
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
and http://support.microsoft.com/?id=257819 for more.

 

[tina]

see your other posts re this question, one to this newsgroup and one to
microsoft.public.access, within the same hour.

 

[Scott]

see your other posts re this question, one to this newsgroup and one to
microsoft.public.access, within the same hour.





Hi Tina, I was new to asking for Help on MS User Group Page. Sorry if I caused you any problems.

 

[Scott]

Hi Scott,

I think it will be possible to solve this one by tweaking some registry
settings. See
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
and http://support.microsoft.com/?id=257819 for more.

This question is for Access and Excel XP (2002). I have an Excel Spreadsheet
(database) that I linked to Access for DB and updating features. My unique
identifier (Key Field) is a Serial Number (SN). The SN's contain 13 digits,
and in some instances digits and a (one) letter i.e.; 99913R218849. When
Linking the Data from Excel to Access, Access identifies all SN as text only
and attaches a ' to the beginning of each SN'd item that contains digits only
(no letter). Inturn making all SN's (with digits only) TEXT realted. This
creates problems when using the Vlookup function in Excel. For example; by
typing in a SN containing digits only no value can be retuned unless you add
the ' to the beginning of the SN. If I remove the ‘ from the SN the Access
DB in return will not show the SN. I really don't want to break the key
field into seperate fields. I have searched MS web site for answers and tried
several methods to correct this problem with no luck. Can anybody help?
Thank You!

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Thanks You Very Much for the Info John! The web site you sent me to proviedes some great info and insight for my problem, but only provides direction to force Access to text out a column, not how to force a text column to numerical column only. This is what I'm really looking for! Of couse this may not be possible? I do appricate the info, and once again Thanks for your help! Scott

 

[John Nurick]

Thanks You Very Much for the Info John! The web site you sent me to
proviedes some great info and insight for my problem, but only provides
direction to force Access to text out a column, not how to force a text
column to numerical column only. This is what I'm really looking for!
Of couse this may not be possible? I do appricate the info, and once
again Thanks for your help! Scott

I should have read your first post more carefully. If you have a text
column in Access there's no way to export a mix of text and numeric
values to Excel: Access uses the ' to force Excel to treat the values as
text. So the best thing is to adjust your Excel lookup formula to work
with text values. This works for me, using TEXT() to convert a numeric
value being looked up into text to match against the text values in the
lookup table:

=VLOOKUP(TEXT(A1,"0"),$C$1:$D$9,2,FALSE)

I suppose a purist might do

=VLOOKUP(IF(ISNUMBER(A1),TEXT(A1,"0"),A1),$C$1:$D$9,2,FALSE)

 

[Scott]

Hi John,

Thanks for the quick response. If I'd written my request a little clearer
you'd have be able to understand it better! After I wrote my question, I
even questioned what I wrote? Sorry! I think the fix you have posted will
help me but my next dilemma is getting the formula to work with my needs
i.e.; Type in a Serial Number (text or numerical value) and return a
specified value from an Excel spreadsheet or Access Database. Right now the
Excel formula I've been using is as follows:

=IF(ISERROR(VLOOKUP(A1,Excel/Access!$B$1:$C$100,2,FASLE)),"",VLOOKUP(A1,Excel/Acess!$b$1:$c$100,2,FALSE))

I'm not sure where to add the TEXT value? I've tried to make the
formula work to no avail. Unfortunately, I know very little about
programming excel formulas when it comes to multiple functions "OUCH"! If
possible, could you PLEASE take a look and tell me where to go with this
formula? It would be greatly appreciated! Thank You Very Much... Scott...

 

[John Nurick]

Hi Scott,

The sample formula I provided looks up the value in A1 just as yours
does. All I did was wrap
A1
in the TEXT() function, so instead of
=VLOOKUP(A1, blah blah
I had
=VLOOKUP(TEXT(A1,"0"), blah blah
..