troubleshooting Question

Deleting all dates instead of specific date range

Avatar of Shanan212
Shanan212Flag for Canada asked on
Microsoft Access
8 Comments2 Solutions261 ViewsLast Modified:

I have the above senario where I am trying to delete a 21 day range of date from Access.

The code is

Sub calleri()
    iMain ("Dec 25, 2011 - Trip Date.xlsx")
End Sub

Sub iMain(PValue As String)

    Dim rSQL As String, dtEndDate As Date
    dtEndDate = CDate(Left(PValue, InStr(PValue, "-") - 1))
    rSQL = "DELETE * FROM [Data] WHERE DateValue([TRIP-DATE]) BETWEEN #" & DateAdd("d", -21, dtEndDate) & "# AND #" & dtEndDate & "#"

    CurrentDb.Execute rSQL, dbFailOnError
    MsgBox dtEndDate & " " & PValue
End Sub

As you can see in the watch window, the date range is correct...since the date range is in December (and all my data is October, the function should not do anything. However, it deletes all of my data in the 'Data' table

The Trip-Date column is compromised of dates but in different format (maybe this has something to do with it? - but it does show 'Date Filters' so it recognizes it as date?)

Any help on overcoming this is appreciated!

Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros