So i have the following query:
DoCmd.RunSQL "delete * from [TABLE NAME] where month = '" & Format(PrevMonth, "yyyy-mm-dd") & "'"
month is a Text field.
Lets say PrevMonth = August 2010 so instead of delete the rows where the date is august 2010, i want it to delete august 2010 and all after that? so september 2010, october 2010, and so on.
thanks.
You can use the CDate() function to cast your text date to date/time data type.
Then:
DELETE FROM [TABLE NAME]
WHERE CDate([month]) >= #2010/08/01#;
I enclosed the field name in brackets because Month() is an Access VBA function ... the brackets let Access know to treat month as a field rather than the function. If it were my database, I would rename the field.
First create a query based on [TABLE Name] where you convert month from text to date
SELECT *,CDate("1 " & [Month]) AS DateDate
Now you can use the dates as normal
FROM Table Name;
DELETE Query1.DateDate
FROM Query1
WHERE (((Query1.DateDate) Between #1/1/2010# And #12/31/2010#));
Hope this helps
