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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,108
Office Version
365
Platform
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
354
Office Version
365, 2016
Platform
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,878
Office Version
365
Platform
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,110
Messages
5,509,265
Members
408,719
Latest member
padapinto

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top