ms access - Trouble building MSAccess SQL query -

i banging head trying figure out how t-sql query format msaccess execute. pretty new msaccess, weekness. trying query pulls info want msaccess:

    select p.person_id, mem.profile_id, nick_name, last_name,  p.postal_code,p.birth_date,p.gender, grade.selected_value      dbo.core_profile_member mem      inner join dbo.core_person p on mem.person_id = p.person_id       left join dbo.evnt_registrant_field grade on mem.person_id = grade.person_id  , mem.profile_id = grade.profile_id , grade.custom_field_id=@gradeid     mem.status_luid <> 316 , mem.profile_id in (@profiles) 

i have been fighting this, , reading kinds of posts, trying figure out how work. have come far, throws syntax error when code gets executing query. here have far doesn't work:

    strsql1 =      "select p.person_id, mem.profile_id, nick_name, last_name, " & _     " p.postal_code, p.birth_date, p.gender (" & _     " dbo_core_profile_member mem inner join dbo_core_person p on mem.person_id = p.person_id " & _     " left join (select person_id, profile_id, selected_value dbo_evnt_registrant_field " & _     " custom_field_id = " & strgradeid & ") grade on mem.person_id = grade.person_id " & _     " , mem.profile_id = grade.profile_id) " & _     " mem.status_luid <> 316 , mem.profile_id in (" & strprofileids & ");" 

i know variables working properly. if debug, , check immediate window after string gets created, can run in mssql management studio , works fine. i'm sure missing subtle msaccess nuance sql generator doesn't like.

anyone have input on need fix? above code, getting "syntax error (missing operator) in query expression..." error.

access requires parentheses in from clause includes more 1 join, , db engine fussy positions.

my guess from clause work. substituted static value, 27, strgradeid.

from     (dbo_core_profile_member mem     inner join dbo_core_person p     on mem.person_id = p.person_id)     left join         (             select person_id, profile_id, selected_value             dbo_evnt_registrant_field             custom_field_id = 27         ) grade     on             mem.person_id = grade.person_id         , mem.profile_id = grade.profile_id 

whether or not got correct, should try setting joins in new query access query designer, if possible. designer knows place parentheses keep db engine happy.

once have working query in designer, using static value strgradeid , static list of values strprofileids, revise vba code produce same sql.