Excel/SQL Hybrid - Run-time Error: Data type mismatch in criteria expression

[Sleepyshy]

Hello!

I am working on a database for entering and pulling old invoices. I am essentially using the excel document as a sql database and limiting user imput on the cells themself to avoid error as many of the people that will use it like to delete things.

How the document works:
The users can select fields from the Combo Boxes that will help them limit their searches. One of these limiting factors is a date range from range 1 to range 2. This is the only thing that I seem to have issues with:

Private Sub cmdShowData_Click()
Dim intSQL As Integer
Dim rng As Range
Dim strHyper As String

    'populate data
    strSQL = "SELECT * FROM [data$] WHERE "
    If cmbProducts.Text <> "" Then
        strSQL = strSQL & " [Company]='" & cmbProducts.Text & "'"
    End If
    
    If cmbID.Text <> "" Then
        If strSQL <> "SELECT * FROM [data$] WHERE " Then
            strSQL = strSQL & " AND [Invoice#]='" & cmbID.Text & "'"
        Else
            strSQL = strSQL & " [Invoice#]='" & cmbID.Text & "'"
        End If
    End If
    
    If cbAccount.Text <> "" Then
        If strSQL <> "SELECT * FROM [data$] WHERE " Then
            strSQL = strSQL & " AND [Account#]='" & cbAccount.Text & "'"
        Else
            strSQL = strSQL & " [Account#]='" & cbAccount.Text & "'"
        End If
    End If
    
    ' THIS IS WHAT IS CAUSING MY GRIEF
    If tbDate1.Text <> "" Then
        If strSQL <> "SELECT * FROM [data$] WHERE " Then
            strSQL = strSQL & " AND [Date]>='" & WorksheetFunction.Text(tbDate1.Value, "yyyy-mm-dd hh:MM:ss") & "' AND [Date]<='" & WorksheetFunction.Text(tbDate2.Value, "yyyy-mm-dd hh:MM:ss") & "'"
        Else
            strSQL = strSQL & " [Date]>='" & WorksheetFunction.Text(tbDate1.Value, "yyyy-mm-dd hh:MM:ss") & "' AND [Date]<='" & WorksheetFunction.Text(tbDate2.Value, "yyyy-mm-dd hh:MM:ss") & "'"
        End If
    End If
    
    If cmbProducts.Text <> "" Or cmbID.Text <> "" Or cbAccount.Text <> "" Or tbDate1.Text <> "" Then
        'now extract data
        closeRS
        
        OpenDB
        
        MsgBox strSQL
        
        ' THIS IS WHERE THE ERROR OCCURS
        rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
        If rs.RecordCount > 0 Then
            Sheets("Search").Visible = True
            Sheets("Search").Select
            Range("dataSet").Select
            Range(Selection, Selection.End(xlDown)).ClearContents
            
            'Now putting the data on the sheet
            ActiveCell.CopyFromRecordset rs
        Else
            MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly
            Exit Sub
        End If
    End If

Set rng = Sheet1.Range("A7")

Do Until rng.Value = ""
    If rng.Offset(0, 6).Value <> "" Then
        strHyper = rng.Offset(0, 6).Value
        rng.Offset(0, 6).Value = "=HYPERLINK(" & Chr(34) & strHyper & Chr(34) & "," & Chr(34) & "Invoice" & Chr(34) & ")"
    End If
    Set rng = rng.Offset(1, 0)
Loop

'Set rng = Sheet1.Range(Sheet1.Range("A7"), rng.Offset(0, 6))

'Sheet1.Sort.SortFields.Clear
'Sheet1.Sort.SortFields.Add Key:=Range("A7"), SortOn:=xlSortOnValues, Order:=xlDescending, _
'    DataOption:=xlSortNormal
'With Sheet1.Sort
'    .SetRange rng
'    .Header = xlGuess
'    .MatchCase = False
'    .Orientation = xlTopToBottom
'    .SortMethod = xlPinYin
'    .Apply
'End With

End
End Sub

To my understanding it is having trouble taking the dates entered in the cells and using it in SQL. It works just fine if you leave the date range blank, it is just when you use it as a search criteria.

The range however is extremely important so I cannot do without.

Let me know if you have any questions and I will do my best to answer them!