delete query ?

[jarheadjim]

i want to set up a query that, when run, deletes all records older than 90 days...I created a delete query, and under my field that is associated with date the criteria is "<now()-90", but it didn't work. did i do something wrong?

 

[Jerry Stoner]

<Now()-90 does work (without the qoutes). If you put qoutes in take them out. If not the problem is somewhere other than your criteria for the query.
HTH
Edit - This assumes your field is set up as a Date/Time format

[This message has been edited by Jerry Stoner (edited 04-18-2002).]

 

[Jerry Stoner]

Right. Thanks Pat.

 

[jarheadjim]

even though i used now() for the date field that that is being queried?

should i go back and change it to date()?

 

[Jerry Stoner]

Yes you probably should. As Pat pointed Now() MAY work but Date() is correct and will ensure you dont run into problems later.

 

[jarheadjim]

It's deleting all records now, not just the ones older than 90 day

. Should the dateadd function be used? If so, how?

thanks again, I'm such a newbie

 

[Jerry Stoner]

Just tried <Date()-90 and it worked fine. Any ideas anyone else?

 

[jarheadjim]

I just realized that it's putting " around 90 after i enter the criteria, and if i take them out, they just come back again. Do I have a setting wrong somewhere?

 

[Jerry Stoner]

Check your properties in the table. Ill bet your field is Text not Date/Time.Set to Date time and in Format set it up the way you want. I usually use short date.

 

[jarheadjim]

OMG, that was it! I can't believe I overlooked it. Thank you so much.

jim

 

[Jerry Stoner]

Your welcome.