Delete Query with Square Brackets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Glenn Peters - 05 Nov 2005 16:16 GMT

Hey guys

Im trying to create a delete query using square brackets. The idea is
that it will delete records older than the number of years that the user
inputs when the query is run.

This is what i have so far

<=Now()-[Years?]

It seems to work fine, eg when i run it, the box appears and i can type
something in. Only problem is, i cant type the number of years, i have
to type the number of DAYS in years to make it work.

For example, i tired to delete records older than 3 years. I cant type
the number '3' i have to type 1095.75 days!

If you could tell me how to change this so i can just type the number of
years instead of the number of days id really appreciate it.

Thanks

G

Reply to this Message

Duane Hookom - 05 Nov 2005 16:45 GMT

You can change your query to calculate the years difference as a column:

YearsDiff: DateDiff("yyyy", [Your Date Field Name], Date())
Then set the criteria to

>=[Enter the number of years]

I would allow the user to enter a date into a control on a form and then
delete records earlier than the date:

WHERE YourDateField <=Forms!frmEnterDate!txtDate

Signature

Duane Hookom
MS Access MVP

> Hey guys
>
[quoted text clipped - 19 lines]
>
> G

Reply to this Message