Trouble building MSAccess SQL query

Question

I am banging my head trying figure out how to get a T-SQL query into a format MSAccess will execute. I am pretty new to MSAccess, so that is my weekness. I am trying to get this query that pulls the info I want into MSAccess:

    SELECT p.person_id, mem.profile_id, nick_name, last_name,  p.postal_code,p.birth_date,p.gender, grade.selected_value
    FROM  dbo.core_profile_member AS mem 
    INNER JOIN dbo.core_person AS p ON mem.person_id = p.person_id  
    LEFT JOIN dbo.evnt_registrant_field AS grade ON mem.person_id = grade.person_id  AND mem.profile_id = grade.profile_id AND grade.custom_field_id=@gradeID
    WHERE mem.status_luid <> 316 AND mem.profile_id IN (@profiles)

I have been fighting with this, and reading all kinds of posts, trying to figure out how to get it to work. This is what I have come up with so far, but it throws a syntax error when the code gets to executing the query. Here is what I have so far that doesn't work:

    strSQL1 = 
    "SELECT p.person_id, mem.profile_id, nick_name, last_name, " & _
    " p.postal_code, p.birth_date, p.gender FROM (" & _
    " dbo_core_profile_member AS mem INNER JOIN dbo_core_person AS p ON mem.person_id = p.person_id " & _
    " LEFT JOIN (SELECT person_id, profile_id, selected_value FROM dbo_evnt_registrant_field " & _
    " WHERE custom_field_id = " & strGradeID & ") AS grade ON mem.person_id = grade.person_id " & _
    " AND mem.profile_id = grade.profile_id) " & _
    " WHERE mem.status_luid <> 316 AND mem.profile_id IN (" & strProfileIDs & ");"

I know the variables are working properly. If I debug, and check the Immediate window after that string gets created, I can actually run it in MSSQL management studio and it works fine. I'm sure I am missing some subtle MSAccess nuance that the SQL generator doesn't like.

Anyone have input on what I need to fix? With the above code, I am getting a "Syntax error (missing operator) in query expression..." error.

HansUp HansUp · Accepted Answer · 2013-07-11T18:11:41

Access requires parentheses in a FROM clause which includes more than one join, and the db engine is fussy about their positions.

My guess is this FROM clause could work. I substituted a static value, 27, for strGradeID.

FROM
    (dbo_core_profile_member AS mem
    INNER JOIN dbo_core_person AS p
    ON mem.person_id = p.person_id)
    LEFT JOIN
        (
            SELECT person_id, profile_id, selected_value
            FROM dbo_evnt_registrant_field
            WHERE custom_field_id = 27
        ) AS grade
    ON
            mem.person_id = grade.person_id
        AND mem.profile_id = grade.profile_id

Whether or not I got that correct, you should try setting up your joins in a new query with the Access query designer, if possible. The designer knows where to place parentheses to keep the db engine happy.

Once you have a working query in the designer, using a static value for strGradeID and a static list of values for strProfileIDs, revise your VBA code to produce the same SQL.

Trouble building MSAccess SQL query

2 Answers