Sharepoint lookup values as number not text

hcabs99

Active Member
Joined
May 9, 2006
Messages
251
HI

Have an issue with a sharepoint linked list with a DB. One of the fields in the sharepoint list performs a look up against another list. On sharepoint this all works fine. I want to use that same lookup in Access when i link up the table. For some reason however, Access is seeing this field as a number not text, so when i try and join it to another table it fails. As its a linked table, i'm unable to change the format of the field.

The source data for the lookup is text , so i am baffled as to why its being set as number.

Any ideas?

Cheers
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
The source data for the lookup is text , so i am baffled as to why its being set as number.
Access does not look at how the data is formatted on the other end.
It looks at something like the first 10 entries, and then tries to "guess" what format it should be.
If the first ten entries only have numbers and no letters, it will probably guess it is a numeric entry, and use Number.

This is a frustration I have faced many times trying to used linked tables, or trying to import Excel files into access.
There are a few ways that I know of to get around this:

1. Change your data to a Text file (i.e. CSV, Tab-Delimited, Space Delimited, etc). Then when you go to import it into Access, it should invoke the Data Import Wizard where you can designate the data type for each field.

2. Make sure that an alphanumeric entry exists in one of the top 10 rows (one that has at least one letter, as long as it is not "E", as that is also used to denote a number with scientific notation).
 

hcabs99

Active Member
Joined
May 9, 2006
Messages
251
sadly does not solve the problem , its a link to the sharepoint table which needs to remain live, not an import, however i've tried to wipe all the source data and just include text in the field and its still importing it as numbers, I've linked loads of sharepoint tables in the past and never come accross this problem . Other fields from the sharepoint site work just fine, just this field with the lookup

i am baffled !
 

baadams

Board Regular
Joined
Mar 2, 2006
Messages
130
Could you create a query from the imported table and create a new calculated field to reformat the data into the format you want? Then you can use the new field within the query for your join.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
357
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I believe the number represents the ID of the record in the lookup list.

Try joining this field to the ID in the lookup table and see if it works as you expect.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,908
Office Version
  1. 365
Platform
  1. Windows
Lookup fields were (sadly) added to Access solely to work with SP lists and now M$ is apparently no longer advocating this relationship - or so I've read. As a result of using a mv field, you see the underlying list value, but what's stored in that table is really the ID field that Access gives it. If you're using a query, you can usually derive the actual value (rather than the id) by using the Value property for that field. No idea what your query looks like or what it's joined to, but if you cannot even join the fields, you may be able to create a query that retrieves the value and then join that to the other table. So if that field is Field1 then you add .Value to the field name: Field1.Value
 

Watch MrExcel Video

Forum statistics

Threads
1,108,931
Messages
5,525,684
Members
409,660
Latest member
1817538628

This Week's Hot Topics

Top