tags:

views:

25

answers:

2

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.

+2  A: 

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.

HansUp
+1  A: 
HighFever
Don't you think HansUp's way works and is easier?
Remou
HansUp version will in one statement, but if CDate fails for any record, my version will allow it easier to check what is going on
HighFever
Thanks, i gave you a vote but its not what I am looking for. I am just an intern here and they have me managing an access program with full of spaghetti code programmed by another student. So adding this would further complicate things for the next guy coming in to maintain this.
masfenix