Link to home
Start Free TrialLog in
Avatar of Shanan212
Shanan212Flag for Canada

asked on

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

Hi,

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

Table: Data
Field: TRIP-DATE

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 = '" & Me.date & "'"

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


Appreciate your time!
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Shanan212

ASKER

Really appreciate it!

Thanks!