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

Posted on 2006-05-25
Medium Priority
Last Modified: 2011-10-03
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!
Question by:paulceaston
LVL 77

Expert Comment

ID: 16761789
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.


Expert Comment

ID: 16761841
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.

LVL 42

Expert Comment

ID: 16762136
>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.

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Author Comment

ID: 16762747
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.  

Author Comment

ID: 16762847
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.

LVL 42

Accepted Solution

dqmq earned 2000 total points
ID: 16764155
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.  

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month15 days, 17 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question