Importing data from Access to Excel using SQL SELECT WHERE <DATE>

I am trying to import specific records from Access Tables to Excel spreadsheets based on a particular Date criteria. The code fails when it tries to execute the sql statement and the error message says

"DataType mismatch"

I have explored as many ways as I could think of or what I could find online to set the data type for the date but nothing seems to work. After modifying the code as it is now, I was able to get rid off the error message but code does not recognize the data in the access table. Any help will be greatly appreciated. Apologies in advance if my question does not make any sense. Trying to get my head around this being a newbie developer... Thanks for your patience.

Public Sub ImportData()

Application.ScreenUpdating = False

'
'       Initialize shtArray (Public Array)
'
        With ThisWorkbook
            shtArray = Array(.Sheets("shtDom"))
        End With

'
'       Initialize tblArray (Public Array)
'
        tblArray = Array("tbl_DOM")


        Dim con As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim dbPath As String
        Dim SQL As String
        Dim i As Integer
        Dim sht As Worksheet, lastRow As Long
        Dim dtDom As String, dtObk As String

        If Weekday(frmInterface.dtPickDomestic, vbMonday) = 1 Then
            dtDom = Format(frmInterface.dtPickDomestic - 3, "dd/mm/yyyy")
        Else
            dtDom = Format(frmInterface.dtPickDomestic - 1, "dd/mm/yyyy")
        End If

        If Weekday(frmInterface.dtPickOtherBanks, vbMonday) = 1 Then
            dtObk = Format(frmInterface.dtPickOtherBanks - 3, "dd/mm/yyyy")
        Else
            dtObk = Format(frmInterface.dtPickOtherBanks - 1, "dd/mm/yyyy")
        End If


        dbPath = ThisWorkbook.Path & "\DOMESTIC SETTLEMENTS.mdb"
        Set con = New ADODB.Connection
        con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

        For i = LBound(tblArray) To UBound(tblArray)

            Select Case i
                Case 0, 8, 9
                    SQL = "SELECT * FROM " & tblArray(i) & " WHERE [BAL_DATE] = #" & dtDom & "#"
                Case 10, 11, 12, 13
                    'SQL = "SELECT * FROM " & tblArray(i) & " WHERE [BAL_DATE] = #" & dtObk & "#"
                Case Else
                    GoTo continue
            End Select                    
                Set sht = shtArray(i)
                lastRow = sht.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
                Set rs = New ADODB.Recordset
                rs.Open SQL, con                    
                    If rs.EOF And rs.BOF Then
                        rs.Close
                        con.Close
                        Set rs = Nothing
                        Set con = Nothing

                        MsgBox "No records!!!", vbCritical
                        Exit Sub
                    End If
                shtArray(i).Range("A" & lastRow).CopyFromRecordset rs                    
                rs.Close
                Set rs = Nothing
continue:
        Next i        
            con.Close
            Set con = Nothing
            Set sht = Nothing

        On Error GoTo 0
        Exit Sub
errHandler:
        Set rs = Nothing
        Set con = Nothing
End Sub