Access Excel Linked Text and Number Issues
 

Question for Access and Excel XP (2002). I have linked an Excel Spreadsheet
(Database) to Access for updating and other special Access functions. The
Excel data contains a unique identifier (Key field) call Serial Numbers (SN)
that contains 13 characters. Some SN contain numbers only, while other SNs
contain numbers and one letter i.e; 99923J143567. When linking the data
together Excel identifies the SNd item as GENERAL, while Access pulls the
information in as TEXT. Access then adds a hash mark (€˜) to the beginning of
each SN containing digits only (no letters) i.e; €˜99923J143467, thus making
the SN TEXT. This in turn causes problems when using the Vlookup function in
Excel, resulting in a no return value when entering a SN, unless the hash
mark (€˜) has been included in the SN. If I remove the Hash Mark (€˜) from the
Excel SNs they will not show up in the Access linked database. I would
prefer not to separate this field if possible. Other then that I have tried
several methods and searched high in low in the MS Database for help with no
LUCK! Can anybody HELP me? Thank You!!!

You could try converting the number to text in the VLookup formula. For
example, with a number in J2:

=VLOOKUP(TEXT(J2,"0"),$A$1:$H$500,2,FALSE)

Scott wrote:
Question for Access and Excel XP (2002). I have linked an Excel Spreadsheet
(Database) to Access for updating and other special Access functions. The
Excel data contains a unique identifier (Key field) call Serial Numbers (SN)
that contains 13 characters. Some SN contain numbers only, while other SNs
contain numbers and one letter i.e; 99923J143567. When linking the data
together Excel identifies the SNd item as GENERAL, while Access pulls the
information in as TEXT. Access then adds a hash mark (€˜) to the beginning of
each SN containing digits only (no letters) i.e; €˜99923J143467, thus making
the SN TEXT. This in turn causes problems when using the Vlookup function in
Excel, resulting in a no return value when entering a SN, unless the hash
mark (€˜) has been included in the SN. If I remove the Hash Mark (€˜) from the
Excel SNs they will not show up in the Access linked database. I would
prefer not to separate this field if possible. Other then that I have tried
several methods and searched high in low in the MS Database for help with no
LUCK! Can anybody HELP me? Thank You!!!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra,

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 also posted this question on the Access side of the discussion group and
a man by the name of John, came up with the same solution/formula as yours.
He is looking into it "I Think"? 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"! I don't want to waste your time If somebody else is working on a
solution. I truly appreciate your feedback, and would like to Thank You Very
Much for your response... Scott...


"Debra Dalgleish" wrote:

You could try converting the number to text in the VLookup formula. For
example, with a number in J2:

=VLOOKUP(TEXT(J2,"0"),$A$1:$H$500,2,FALSE)

Scott wrote:
Question for Access and Excel XP (2002). I have linked an Excel Spreadsheet
(Database) to Access for updating and other special Access functions. The
Excel data contains a unique identifier (Key field) call Serial Numbers (SN)
that contains 13 characters. Some SN contain numbers only, while other SNs
contain numbers and one letter i.e; 99923J143567. When linking the data
together Excel identifies the SNd item as GENERAL, while Access pulls the
information in as TEXT. Access then adds a hash mark (€˜) to the beginning of
each SN containing digits only (no letters) i.e; €˜99923J143467, thus making
the SN TEXT. This in turn causes problems when using the Vlookup function in
Excel, resulting in a no return value when entering a SN, unless the hash
mark (€˜) has been included in the SN. If I remove the Hash Mark (€˜) from the
Excel SNs they will not show up in the Access linked database. I would
prefer not to separate this field if possible. Other then that I have tried
several methods and searched high in low in the MS Database for help with no
LUCK! Can anybody HELP me? Thank You!!!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html