Using Excel as the Database- getting error on sub query

[Conrado Natac]

'this the code

Option Explicit

Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String

Public Sub OpenDB()
           If cnn.State = adStateOpen Then cnn.Close
           cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
                                    ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name
           cnn.Open
End Sub

Public Sub closeRS()
           If rs.State = adStateOpen Then rs.Close
           rs.CursorLocation = adUseClient
End Sub

'this is the code with the error
Private Sub cmdbalance_Click()
'this gets the balance of an invoice, after payments
' per one customer
      
      
        Sheets("View").Visible = True
        Sheets("View").Select
        Range("A30:N40000").ClearContents
      
      
        closeRS
        OpenDB

        closeRS
      
        'this is the connrection
        OpenDB
      
  'this keeps getting the error              
          
          strSQL = "SELECT  C1.[Cust Name], c1.[Date],  C1.[Due Date] from [data$] as c1,  [data$]  where c1.[Cust #] in SELECT [Apply to], Sum([Amount]) as InvBal FROM [data$] where [data$].[Cust #]= " & "'" & cmbcustno.Text & "'" & " group by [Apply to]"

          rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
                
        'dispose the connection
        Set cnn = Nothing

        If rs.RecordCount > 0 Then
            Sheets("View").Visible = True
            Sheets("View").Select
            'Range("dataSet").Select
            'Range(Selection, Selection.End(xlDown)).ClearContents
          
            Range("A30").Select
                      
            'Now putting the data on the sheet
            ActiveCell.CopyFromRecordset rs
          
       Else
            'dispose the recordeset
            Set rs = Nothing
      
            Exit Sub
        End If      
'dispose the recordeset
Set rs = Nothing

End Sub