locked
Sharepoint 2007 Lists + Access 2007 Query: "type mismatch in expression" RRS feed

  • Question

  • I have the following lists defined in SharePoint  Server 2007:

    List:  “GIO Groups”

    Column:  GIO Group,  Single line of text

    Column:  GIO Group Mgr, Person or Group

    List:  “GIO Teams”                                      

    Column:  GIO Group,  Lookup in “GIO Groups” / Field:  “GIO Group”

    Column:  GIO Team,  Single line of text                            

    Column:  GIO Team Mgr,  Person or Group                      

    In Access 2007, I link to each list. 

    I create a query using “GIO Groups” and “GIO Teams”, joining the “GIO Group” columns.  The SQL looks like this:

    SELECT [GIO Groups].[GIO Group], [GIO Groups].[GIO Group Mgr], [GIO Teams].[GIO Team], [GIO Teams].[GIO Team Mgr]

    FROM [GIO Groups] LEFT JOIN [GIO Teams] ON [GIO Groups].[GIO Group] = [GIO Teams].[GIO Group];

    But when I execute the query, I get “type mismatch in expression”

    I think this has something to do with the fact that [GIO Teams].[GIO Group] is a lookup column on SharePoint.

    Do you know of a workaround?



    • Moved by Mike Walsh FIN Friday, May 6, 2011 6:32 PM admin q (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
    Friday, May 6, 2011 4:44 PM

Answers

  • Hi funnybroad,

     

    Thanks for your post.

    Instead of using “[GIO Groups].[GIO Group]” on the join process, please try to use “[GIO Groups].ID” .

    Before:

    SELECT [GIO Groups].[GIO Group], [GIO Groups].[GIO Group Mgr], [GIO Teams].[GIO Team], [GIO Teams].[GIO Team Mgr]

    FROM [GIO Groups] LEFT JOIN [GIO Teams] ON [GIO Groups].[GIO Group] = [GIO Teams].[GIO Group];

     

    After:

    SELECT [GIO Groups].[GIO Group Mgr], [GIO Groups].[GIO Group], [GIO Teams].[GIO Group], [GIO Teams].[GIO Team], [GIO Teams].[GIO Team Mgr]

    FROM [GIO Groups] INNER JOIN [GIO Teams] ON [GIO Groups].ID = [GIO Teams].[GIO Group];

     

    Test and share your result.

     

    Thanks,

    Wayne

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com


    • Marked as answer by GuYuming Monday, May 23, 2011 2:46 AM
    Tuesday, May 10, 2011 11:45 AM