Syntax error (missing operator) in query expression

Question

Hi there, This is a basic Search form that returns results based on the criteria selected and then the user can double click to open the record.  I have a few of these in my database and actually just copied one and edited the field names and the SQL.  When I click Search, no results are returned, I get the error message "Syntax eror (missing operator) in query expression [RES_APP_ID] and when I debug - the bolded line below is highlighted.  I cannot figure out what I am doing wrong.  I have checked and absolutely everywhere, that field is called RES_APP_ID.  Can someone please help me?

Thanking you in advance!

Private Sub List_Results_DblClick(Cancel As Integer)

    'Open MAIN_Form based on the ID from List_Results listbox

DoCmd.OpenForm "Frm_Resources_MULTIPLES", , , "[RES_APP_ID] =" & Me.List_Results, , acDialog    DoCmd.Close acForm, Me.Name

End Sub

Private Sub Search_Records_Click()

'Set the Dimensions of the Module

    Dim StrSQL As String, strOrder As String, strWhere As String

    Dim dbNm As Database

    Dim qryDef As QueryDef

    Set dbNm = CurrentDb()

'Constant Select statement for the Row Source

    StrSQL = "SELECT SUBTBL_RESOURCE_APPLICATIONS.RES_APP_ID, SUBTBL_RESOURCE_APPLICATIONS.APPROVAL_NUM, TBL_GOVT_POOLS.POOL, TBL_GOVT_FIELDS.FIELD, TBL_RES_APPTYPE.RES_APPTYPE, TBL_RESOURCE_APPLICATION_TYPES.APPLICATION_TYPE" _

& " FROM TBL_RES_APPTYPE RIGHT JOIN (((TBL_GOVT_POOLS RIGHT JOIN (TBL_GOVT_FIELDS RIGHT JOIN SUBTBL_RESOURCE_APPLICATIONS ON TBL_GOVT_FIELDS.FIELD_ID = SUBTBL_RESOURCE_APPLICATIONS.FIELD) ON TBL_GOVT_POOLS.POOL_ID = SUBTBL_RESOURCE_APPLICATIONS.POOL) LEFT JOIN SUBTBL_RES_APP_DETAIL ON SUBTBL_RESOURCE_APPLICATIONS.RES_APP_ID = SUBTBL_RES_APP_DETAIL.RES_APP_ID) LEFT JOIN TBL_RESOURCE_APPLICATION_TYPES ON SUBTBL_RES_APP_DETAIL.APPLICATION_CTGY = TBL_RESOURCE_APPLICATION_TYPES.RESAPP_ID) ON TBL_RES_APPTYPE.APPTYPEID = SUBTBL_RES_APP_DETAIL.RES_APPTYPE;" _

    strWhere = "WHERE"

    strOrder = "ORDER BY SUBTBL_RESOURCE_APPLICATIONS.[RES_APP_ID];"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form

    If Not IsNull(Me.Res_App_ID) Then '<--If the textbox RES_APP_ID contains no data THEN do nothing

    strWhere = strWhere & " (SUBTBL_RESOURCE_APPLICATIONS.[RES_APP_ID]) Like '*" & Me.Res_App_ID & "*'  AND" '<--otherwise apply the LIKE statement to the QueryDef

    End If

    If Not IsNull(Me.cbo_Field) Then '<--If the combo Field contains no data THEN do nothing

    strWhere = strWhere & " (TBL_GOVT_FIELDS.[FIELD]) Like '*" & Me.cbo_Field & "*'  AND" '<--otherwise apply the LIKE statement to the QueryDef

    End If

etc..

Answer 1

Oh good Lord - I just carried out a semi-colonoscopy in public!

Glad I was able to help.

Answer 2

John, I cannot thank you enough, 4 hours I spent on this and it was the semicolon!!!

Answer 3

Step through the code and look at the values of strWhere and strSQL. I don't see anywhere that you're building a complete SQL string; you have a probably invalid semicolon after RES_APPTYPE (if you're concatenating other stuff on to it); your strWHERE ends in AND (which would be invalid)...

What is the actual SQL string that's causing the error, in other words?