Delete Query with Square Brackets

  • Thread starter Thread starter Glenn Peters
  • Start date Start date
G

Glenn Peters

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
 
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]
Click to expand...

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

--
Duane Hookom
MS Access MVP


Glenn Peters said:
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
Click to expand...
 
Back
Top