Delete Query

Hunter J.

Registered User.
Local time
Yesterday, 18:38
Joined
Feb 1, 2004
Messages
24
I am working on a delete query to delete data from one table. I want to delete the data that is greater then 3 months. How would I type this in the criteria?

Thanks for any help.
Hunter J.
 
Basically:
<DateDiff("m",-3,now())
 
FoFa said:
<DateDiff("m",-3,now())
I suspect that should be DateAdd, not DateDiff.
 
Last edited:
Both ways give me the whole table to delete...

Thanks,
Hunter J
 
Where YourDate < DateAdd("m",-3, Date());

The DateAdd() function will be more accurate than the DateDiff() function for this purpose since the following expression returns a difference of 1 month - datediff("m", #1/31/4#, #2/1/4#)

PS - use Date() rather than Now() unless you really want to take time of day into consideration.
 
I am still getting all the records using.....

DateAdd("d",-3,Date())

I am using the day to test with since at this time I don't have 3 months of data...I am trying to plan ahead.

Again, Thanks for all the help and understanding.
Hunter J.
 
Can you post a sample db with the table and query in it, or at least the whole SQL?
 
DELETE [040709AF].*, [040709AF].Date
FROM 040709AF
WHERE ((([040709AF].Date)=DateAdd("d",-3,Date())));

Hope this is what you are asking for. Sorry, I am very new to this.

Thanks,
Hunter J.
 
That's the SQL, yes. You're using an "=" instead of "<" as the examples showed. I assume you are only deleting data that has a date of 3 days ago, instead of anything older like you want.

By the way, you should not have a field named "Date". It can conflict with the Date() function (may even be causing a problem here):

http://support.microsoft.com/default.aspx?scid=kb;EN-US;209187
 
I can't test again until Thrusday but I will look into the name "date" to see if its causing problems.

Thanks for all the help.
Hunter J.
 
Update.

It worked great. My problem was I was inserting "=" when it should have been "<" like FoFo, Pat and pbaldy poined out.
This site is great because of the people helping here.

Thanks Again.
Hunter J
 
Last edited:

Users who are viewing this thread

Back
Top Bottom