troubleshooting Question

Deleting rows based on date range that is parsed from file name

Avatar of Shanan212
Shanan212Flag for Canada asked on
Microsoft Access
2 Comments1 Solution279 ViewsLast Modified:

I am (going to) writing an access macro to delete a range of dates that exist in

Table: Data

This date would be parsed from an excel file name that exists in a specific folder. I have the functions to check if a file exist in a folder, etc.

The file name would be "Nov 25, 2011 - Trip Data.xlsx"

In the above case, I want to delete all records from the table called "DATA" with the "TRIP-DATE" being in the range of 11/25/2011 to 11/06/2011 (Nov 6 or 21 days before or prior 3 weeks)

Let alone I have another problem of deleting all dates that comes within this range - not nessarily beginning and the end date (but thats aside)

How can I decode the above file name just to get the date portion and how would I delete the range? I am asking 2 questions because I've made some progress in delete portion; please let me know if the questions require 2 parts.

I am guessing the file name has to be put through an excel-like formula (eg: left(filename, 12)?)

I did some research and found this (the date is in text form so the quotes are added at the end)

rSQL = "delete * from Data WHERE TRIP-DATE = '" & & "'"

So I am guessing I would have to run the above code for each date (by looping?)

Appreciate your time!
Join our community to see this answer!
Unlock 1 Answer and 2 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 1 Answer and 2 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