Sharepoint lookup values as number not text

[hcabs99]

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

 

[Joe4]

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]

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]

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]

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]

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