CODEWITHSUNDEEP
sqlvbams-access
r_mo
r_mo

Reputation: 1

How to find a syntax error in my SQL code?

I am trying to compile my code but I get the same error every time:

Syntax error (missing operator ) in query expression ' True Status.Subsystem Not LIKE '''

This is my code :

Sub Import_Loop_Check_list()

Dim strSQL As String
Dim SS_sel As String
Dim rcrd As DAO.Recordset

If IsNull(Cobsubsystem) Then
    SS_sel = "True"
   Else
    If IsNull(Logic1) Then
        SS_sel = "Status.Subsystem LIKE '" & Cobsubsystem & "' "
   Else
        SS_sel = "Status.Subsystem NOT LIKE '" & Cobsubsystem & "' "
    End If
End If

strSQL = " SELECT DISTINCT LOOP_JB.Loop_name, [Easyplant Dump query].Subsystem, LOOP_JB.PANEL_FROM, LOOP_JB.ITR_PANEL_FROM, LOOP_JB.ITR_PANEL_FROM_state, LOOP_JB.CABLE_NUM, LOOP_JB.ITR_cable, LOOP_JB.ITR_STATE_Cable, LOOP_JB.Cabinet_JB, LOOP_JB.ITR_Cabinet_JB, LOOP_JB.ITR_STATE_CABINET, Multicors.CABLE_NUM AS Multicore, Multicors.ITR_PANEL_FROM, Multicors.ITR_PANEL_FROM_state, [Cabinet query].PANEL_TO, [Cabinet query].ITR_PANEL_TO, [Cabinet query].ITR_PANEL_TO_state INTO [LOOP_Check] " & _
         " FROM (LOOP_JB INNER JOIN ([Cabinet query] RIGHT JOIN Multicors ON [Cabinet query].CABLE_NUM = Multicors.CABLE_NUM) ON LOOP_JB.Loop_name = Multicors.Loop_name) INNER JOIN [Easyplant Dump query] ON LOOP_JB.Loop_name = [Easyplant Dump query].Clean_Tag_Number" & _
         " WHERE True " & SS_sel & strSQL

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

DoCmd.OpenTable "LOOP_Check"

End Sub

Upvotes: 0

Views: 103

Answers (1)

Hogan
Hogan

Reputation: 70513

I looked at this again -- something else does not make sense.

You reference Status.Subsystem in the WHERE but there is no table named Status -- Did you not include the full query?

original answer

I think the error message is clear -- you have a strange where statement

WHERE True Status.Subsystem Not LIKE

you probably mean

WHERE Status.Subsystem Not LIKE

so change this line

     " WHERE True " & SS_sel & strSQL

to this

     " WHERE " & SS_sel & strSQL

Also, it does not right to me -- are you sure you want to do a RIGHT join to Multicors and not a left join? You want a row in your result for every row in the multicors table?

Upvotes: 1

Related Questions