VBA&SQL - delete date range from database

Question

I am trying to create an option on my Access database that deletes all records between 2 dates.

I code I have below does not seem to work consistently. For example, if my database has dates 01/01/18 to 01/30/18, and the range I specify is exactly 01/01/18 to 01/30/18, then it works and deletes all data.

But if I specify any other date range (like 01/01 - 01/15), it will fail and no records will be deleted.

The [Trade Date] is in short text format instead of date, but all the entries are in MM/DD/YY. Would prefer to keep it this way unless that is the issue and no other alternatives.

Please let me know what I am doing wrong or could do better. Thank you in advance.

Dim trFrmDat As String
Dim trToDat As String
Dim dbsDelete As DAO.Database
Dim qdfToDelete As DAO.QueryDef
Dim countString As String
Dim count As Long

Set dbsDelete = CurrentDb
trFrmDat = InputBox("Trade Date To Be Deleted From [MM/DD/YY]:")
trToDat = InputBox("Trade Date To Be Deleted To [MM/DD/YY]:")
If Len(trFrmDat) <> 8 Or Len(trToDat) <> 8 Then
    MsgBox ("The correct date or answer has not been entered. Process Aborted.")
    Exit Sub
Else
    countString = "SELECT COUNT(PK_ID) FROM AR_Consolidated WHERE [Trade Date] BETWEEN " & trFrmDat & " AND " & trToDat & ""
    count = dbsDelete.OpenRecordset(countString).Fields(0).Value
    Set qdfToDelete = dbsDelete.CreateQueryDef("", "DELETE FROM AR_Consolidated WHERE [Trade Date] BETWEEN " & trFrmDat & " AND " & trToDat & "")
    qdfToDelete.Execute dbFailOnError
    MsgBox ("" & count & " records have been deleted from AR_Consolidated")
End If

EDIT:

I ended up using one of the suggestions below, but still had formatting issues with the date, so I conceded I cannot keep the actual field as short text. I just injected an alter line and everything works perfectly.

    DoCmd.RunSQL "ALTER TABLE AR_Consolidated ALTER COLUMN [Trade Date] Datetime"

Answer 1

I would take a different approach and use parameters instead of string concatenation.

The query's SQL would be something like this:

PARAMETERS [prmDateFrom] DateTime, [prmDateTo] DateTime;
DELETE *
FROM AR_Consolidated
WHERE [Trade Date] Between [prmDateFrom] And [prmDateTo];

Then to call my query in VBA:

Sub Something()

    Dim trFrmDat As String
    Dim trToDat As String

    trFrmDat = InputBox("Trade Date To Be Deleted From [MM/DD/YY]:")
    trToDat = InputBox("Trade Date To Be Deleted To [MM/DD/YY]:")

    If Len(trFrmDat) <> 8 Or Len(trToDat) <> 8 Then
        MsgBox ("The correct date or answer has not been entered. Process Aborted.")
        Exit Sub
    End If

    Dim qdf As DAO.QueryDef
    Dim count As Long

    Set qdf = CurrentDb().QueryDefs("QueryName")
    With qdf
        .Parameters("[prmDateFrom]").Value = CDate(trFrmDat)
        .Parameters("[prmDateTo]").Value = CDate(trToDat)
        .Execute dbFailOnError
        count = .RecordsAffected
    End With

    MsgBox " " & count & " records have been deleted from AR_Consolidated"

End Sub

However, you need to enhance your input validation as a normal string with 8 characters which cannot be converted to a date would cause a runtime error.