Syntax Error in FROM clause

[cjohnston]

Good Morning! I am currently a little bit stumped with a query that I am building. To simplify it, I have a database for tracking jobs. In order to filter the jobs I am using a multiselect list box as one of my controls. If the length of the box is less than a certain value it equates to showing all ships having jobs based on criteria from an option group. If it exceeds that value, then only the selected ships output data. My issue is that the 'ALL' function has a syntax error in the From clause, whereas if I select any number of ships, there is no error. My code is below. thank you for any assistance you guys may be able to provide.

Private Sub btnSrchRecord_Click()
    'Declarations
     Dim strWhere As String     'Criteria String
     Dim ctl As Control
     Dim lngLen As Long         'Length of the criteria string to append to.
     Dim db As DAO.Database
     Dim qdf As DAO.QueryDef
     Dim strSQL As String
     Dim stDocName As String     'Name of document to be opened
     Dim varItem As Variant      'Selected items in multi-select
     Dim strDescrip As String    'Description of WhereCondition
     Dim strDelim As String      'Delimiter for this field type.
     Set db = CurrentDb
     Set qdf = db.QueryDefs("qrySearch")
     Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
     
     '**********************************Query criteria**********************************************
     For Each varItem In Me!lstShip.ItemsSelected
    strWhere = strWhere & ",'" & Me!lstShip.ItemData(varItem) & "'"
 Next varItem
 
 'test box
 'MsgBox Len(strWhere)
 
 
    If Len(strWhere) = 0 Then
                         MsgBox "You did not select anything from the list" _
                         , vbExclamation, "Nothing to find!"
                         Exit Sub
                    End If
    If Len(strWhere) < 7 Then
        Select Case Me.optionSearch
            Case 1
                ' Remove the leading comma from the string
                    strWhere = Right(strWhere, Len(strWhere) - 1)
                ' Build the new SQL statement incorporating the string
                    strSQL = "SELECT data.* FROM data" & _
                     "WHERE (data.[Turned_On]) IS NULL " & _
                     "ORDER BY data.[Ship];"
            Case 2
                ' Remove the leading comma from the string
                    strWhere = Right(strWhere, Len(strWhere) - 1)
                ' Build the new SQL statement incorporating the string
                    strSQL = "SELECT data.* FROM data" & _
                     "WHERE (data.[funded]) IS NULL " & _
                     "ORDER BY data.[Ship];"
            Case 3
                ' Remove the leading comma from the string
                    strWhere = Right(strWhere, Len(strWhere) - 1)
                ' Build the new SQL statement incorporating the string
                    strSQL = "SELECT data.* FROM data" & _
                     "WHERE (data.[Turned_On]) IS NULL " & _
                     "AND (data.[funded]) IS NULL " & _
                     "ORDER BY data.[Ship];"
            Case 4
                ' Remove the leading comma from the string
                    strWhere = Right(strWhere, Len(strWhere) - 1)
                ' Build the new SQL statement incorporating the string
                    strSQL = "SELECT data.* FROM data" & _
                     "ORDER BY data.[Ship];"
            End Select
        Else
            Select Case Me.optionSearch
                Case 1
                    ' Remove the leading comma from the string
                    strWhere = Right(strWhere, Len(strWhere) - 1)
                    ' Build the new SQL statement incorporating the string
                    strSQL = "SELECT data.* FROM data " & _
                     "WHERE (data.[Turned_On]) IS NULL " & _
                     "AND data.[Ship] IN(" & strWhere & ")" & _
                     "ORDER BY data.[Ship];"
                 Case 2
                    ' Remove the leading comma from the string
                    strWhere = Right(strWhere, Len(strWhere) - 1)
                    ' Build the new SQL statement incorporating the string
                    strSQL = "SELECT data.* FROM data " & _
                     "WHERE (data.[funded]) IS NULL " & _
                     "AND data.[Ship] IN(" & strWhere & ")" & _
                     "ORDER BY data.[Ship];"
                 Case 3
                    ' Remove the leading comma from the string
                    strWhere = Right(strWhere, Len(strWhere) - 1)
                    ' Build the new SQL statement incorporating the string
                    strSQL = "SELECT data.* FROM data " & _
                     "WHERE (data.[funded]) IS NULL " & _
                     "WHERE (data.[Turned_On]) IS NULL " & _
                     "AND data.[Ship] IN(" & strWhere & ")" & _
                     "ORDER BY data.[Ship];"
                 Case 4
                    ' Remove the leading comma from the string
                    strWhere = Right(strWhere, Len(strWhere) - 1)
                    ' Build the new SQL statement incorporating the string
                    strSQL = "SELECT data.* FROM data " & _
                     "WHERE data.[Ship] IN(" & strWhere & ")" & _
                     "ORDER BY data.[Ship];"
             End Select
         End If
        
                           
           
                       
         
  
     '******************************End query criteria**********************************************
     
     qdf.SQL = strSQL 'Sends criteria to query
     'Reset query
     If CurrentProject.AllForms("frmSearch").IsLoaded Then
         Forms!frmSearch.Requery
     End If
     

   'Open required document
    stDocName = "frmData"
    DoCmd.OpenForm stDocName, acViewForm
    'Reset Form after search
    Set qdf = Nothing
    Set db = Nothing
    strWhere = ""
    
    
    
    For Each ctl In Me.Section(acDetail).Controls
        Select Case ctl.ControlType
        Case acTextBox, acListBox
            ctl.Value = Null
        Case acRadioButton
            ctl.Value = False
        End Select
    Next
    
End Sub

[orange]

It appears that you NEED A SPACE at the end of some of your strings

Get into the habit of endiing such strings with an extra space or two. It's a common problem.

For example, this partial string will look like

strSQL = "SELECT data.* FROM data" & _ "WHERE (data.[Turned_On]) IS NULL " & _

SELECT data.* FROM dataWHERE (data.[Turned_On]) IS NULL

You could put some debug.print statements in your code to show you what your SQL SELECT statements look like.

[cjohnston]

That solved it. Thank you very much....This has been driving me nuts for over an hour.