Delete Query

[Hunter J.]

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.

 

[FoFa]

Basically:
<DateDiff("m",-3,now())

 

[pbaldy]

<DateDiff("m",-3,now())

I suspect that should be DateAdd, not DateDiff.

 

[Hunter J.]

Both ways give me the whole table to delete...

Thanks,
Hunter J

 

[Pat Hartman]

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.

 

[Hunter J.]

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.

 

[pbaldy]

Can you post a sample db with the table and query in it, or at least the whole SQL?

 

[Hunter J.]

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.

 

[pbaldy]

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

 

[Hunter J.]

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.

 

[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