SQL Query - Date Range Not working Properly

Question

I need your help to review the code below. I have an Access Database with userform entry data that contains a column with the submission date of the entry...Essentially what this code is meant to do, it's supposed to gather all entries between a specific data range that's entered in a UserForm on a VBA application and populate all entries between those dates on an Excel sheet. So far I've been able to somewhat get results with the code below but it's not behaving as intended...

The issue is that when for example..I have 3 submissions one for the 8th, One for the 9th and one for the 10th of December...If I select December 1st-11th nothing comes up in the list...When I select 1st-12th of December then all those 3 are populated..If I select from previous month to December 12th, nothing gets populated...Can you please take a look at the code below and let me know what you think :)

This is How the data is stored to the access server (just in case that would be the issue I'm including this)

  Dim todaydate As DateTime
    Dim time As Date



    todaydate = DateTime.Now.ToString("dd/MM/yyyy")
    time = DateTime.Now.ToString("HH:mm:ss")

    hideform()

    Panel_RenewForm.Width = 636
    Panel_RenewForm.Height = 201
    Panel_RenewForm.Visible = True


    Panel_RenewForm.Location = New Point(12, 191)
    Btn_Submit.Visible = False
    Btn_Clear.Visible = False



    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Dim myConnection As OleDbConnection = New OleDbConnection


    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
    dataFile = "C:\Users\ssroujian\Documents\nsltrackerreport.accdb"
    connString = provider & dataFile
    myConnection.ConnectionString = connString
    myConnection.Open()

    Dim str As String


    ' remember to add the ID of every column in the access database here
    str = "Insert into nsltrackerreport([CBSID],[AgentName],[Supervisor],[SkillSet],[Location],[DOH],[AccountNumber],[SupportType],[CallDescription],[CallDetails],[Resolution],[FollowupRequired],[ColdTransfer],[VerifiedPipe],[MissInformed],[PrevCBSID],[NSLAgent],[SubmitDate],[SubmitTime]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)




    'this will pass values of controls to the access database to the designated column.
    cmd.Parameters.Add(New OleDbParameter("CBSID", CType(Combo_CBSID.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("AgentName", CType(Combo_AgentName.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("Supervisor", CType(Combo_Supervisor.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("SkillSet", CType(Combo_SkillSet.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("Location", CType(Combo_Location.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("DOH", CType(combo_DOH.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("AccountNumber", CType(txt_AccountNumber.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("SupportType", CType(Combo_SupportType.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("CallDescription", CType(Combo_CallDescription.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("CallDetails", CType(Combo_CallDetails.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("Resolution", CType(txt_Resolution.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("FollowupRequired", CType(txt_FollowupRequired.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("ColdTransfer", CType(txt_ColdTransfer.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("VerifiedPipe", CType(txt_VerifiedPipe.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("MissInformed", CType(txt_AgentMissInformed.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("PrevCBSID", CType(Combo_Prev_AgentCBSID.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("NSLAgent", CType(lbl_NSLAgentName.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("SubmitDate", CType(todaydate, String)))
    cmd.Parameters.Add(New OleDbParameter("SubmitTime", CType(time, String)))




    Try

        cmd.ExecuteNonQuery()
        cmd.Dispose()
        myConnection.Close()


    Catch ex As Exception
        MsgBox("Unable to connect to NSL Tracker reporting database, please contact administrator and advise of the error below :" & vbCrLf & vbCrLf & ex.Message, vbCritical, "Connection Unsuccessful")

        Exit Sub

    End Try



    clearfields()


End Sub

And this is how it is captured in the Excel file based on date range selected:

Dim i                   As Long
Dim CN                  As New ADODB.Connection
Dim RS                  As New ADODB.Recordset
Dim FSO                 As New FileSystemObject
Dim F                   As File
Dim DBPassword          As String
Dim strSQL              As String
Dim DestinationSheet    As Worksheet

On Error Resume Next
Set F = FSO.GetFile("C:\Users\ssroujian\Documents\nsltrackerreport.accdb")
On Error GoTo 0
If F Is Nothing Then
    GoTo ExitSub:
End If
DBPassword = ""
Set DestinationSheet = Worksheets("Sheet1")
'Use SQL's SELECT and FROM statements for importing Table.

strSQL = "SELECT nsltrackerreport.* FROM nsltrackerreport WHERE SubmitDate >= #" & DTPickerCtrl1.Value & "# AND SubmitDate <= #" & DTPickerCtrl2.Value & "#"

'connection string
CN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "C:\Users\ssroujian\Documents\nsltrackerreport.accdb" & ";Jet OLEDB:Database Password=" & DBPassword
'Open connection
CN.Open
RS.Open strSQL, CN, , , adCmdText
'Clear the destination worksheet.
DestinationSheet.Cells.Clear
Sheet1.Range("A3").CopyFromRecordset RS

    'Reinstate field headers (assumes a 4-column table).
    'Note that the ID field will also transfer into column A,
    'so you can optionally delete column A.
    Sheet1.Range("A1:U1").Value = _
    Array("ID", "CBSID", "AgentName", "Supervisor", "SkillSet", "Location", "DOH", "AccountNumber", "SupportType", "CallDescription", "CallDetails", "Resolution", "FollowupRequired", "ColdTransfer", "VerifiedPipe", "MissInformed", "PrevCBSID", "PrevAgent", "NSLAgent", "SubmitDate", "SubmitTime")

RS.Close
CN.Close

ExitSub:

Set RS = Nothing
Set CN = Nothing
Set F = Nothing
Set FSO = Nothing

Here's what a msgbox of the SQLstr gives:

Answer 1

Use yyyy-m-d format for the date values you submit to the Access db engine.

strSQL = "SELECT nsltrackerreport.* FROM nsltrackerreport " & _
    "WHERE SubmitDate >= #" & Format(DTPickerCtrl1.Value, "yyyy-m-d") & _
    "# AND SubmitDate <= #" & Format(DTPickerCtrl2.Value, "yyyy-m-d") & "#"