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

Posted on 2006-05-25
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

    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.

    LVL 3

    Expert Comment

    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

    >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.


    Author Comment

    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

    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

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now