"Type mismatch in expression" error when running query using two joined linked Sharepoint datasheets

I am getting a "Type mismatch in expression" error when running query using two joined linked Sharepoint datasheets.  

One datasheet is "Custodians".  It is hosted on a Sharepoint server.
The other datasheet is "BoxTracker".  It is also hosted on a Sharepoint server.

The BoxTracker datasheet has a lookup field, titled "Custodian" linked to the "Full Name" field of the Custodians datasheet.  This is the only link between the two datasheets in Sharepoint and also in Access as shown in the Relationships view and in the query design view when both tables are shown.

Both datasheets are linked to the MS Access database I am building.  I have query that show selected fields from both datasheets.

Whenever I try to run the query I get the "Type mismatch..." error.  There is nothing in sharepoint to set the "type" for a lookup field.  I would assume that the type would be the same as the filed it was looking up the data from.

Any help would be greatly appreciated.  While I don't think this problem is "Extremely Difficult", it is urgent.  

Thank you in advance!
Who is Participating?
It's just a case where Access has assumed too much from your field names.

First, delete the join relationship between the Custodian lookup field and full name.  Manually create a relationship between the custodian lookup field and the ID column.

When you define the lookup field (and this applies whether in table design or form design), your rowsource should be "select ID, FullName from tblCustodian". Set bound column = 1 and set column width to 0;.

That way, you link (bind) the lookup field to the ID column, but you hide the bound column so that the name column appears in the datasheet grid.  
Hi paulceaston,

What are you storing in the table for your lookup field?
Note - what you are storing is not necessarily the same as what you can see.  That is the problem with lookup fields.

Hi Paul

Try linking the tables to your access database and then opening in design view top see the field properties. Whilst you cannot change the properties lets check the basics and that the two fields are the compatable ie the types are the same and that if number fields that they are the same types.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

>The BoxTracker datasheet has a lookup field, titled "Custodian" linked to the "Full Name" field of the Custodians datasheet.  

You see, that might indicate the problem.  My hunch is that boxtracker should be linked to the primary key of the Custodian table, not to Full Name.  My hunch is that the boxtracker table contains a numeric field and you get the error because you are trying to join to a text field.  

Of course, this is all conjecture, if you reveal the relevant parts of your table structures we can answer more confidently.

paulceastonAuthor Commented:
Ahhhh...light bulb goes on.  I opened the BoxTracker linked table in design view in Access.  The Custodian lookup field DataType is given as Number.

So I should link to the sharepoint autonumbered "ID" column?  But the relationship is autmatically created when I link the tables.  
paulceastonAuthor Commented:
So how do I work around this?  I want to keep the look up field to ensure that anyone adding record in the Sharepoint "BoxTracker" list can select from the list of custodians in the box tracker's "Custodian" field.  This ensures consistent spelling, avoids, typos, etc.

But if I use the lookup field, in Sharepoint, then I can not create queries from the linked tables in MS Access.

My goal for this query is to show all completely reviewed boxes by custodian.  I'm using an Access database that I've created in the past and am replacing all the tables with linked Sharepoint lists/datasheets.

So far this is working very well with the exception of sharepoint lookup fields.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.