What criteria for a date to expire?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am doing a database for multiple training items that expire every year.
What I want to do is make it possible to track who will be expiring in the
next 30 days. I also want to make an experation date in the database. This is
what i have

Name
Date Trained
Expires

I want to set the criteria in the query to automaticlly set the experation
date from the date entered in the Date Trained Field. I have no idea what to
put in the criteria for that and have not found an example anywher. Can you
please help?

Scott
 
Well, you would fill in the "Expires" field when you add the record, not
using a query. But, storing the date and the expiration seems rather
redundant if they will always be one year apart.

Instead, just look for every record where the Date Trained was between 11
months prior to today's date and 12 months prior to today's date.
 
I don't quit understand what your saying, but then again I'm new to access.
The reason I have the expires in there is so I can look up triaing dates per
person and per subject trained on. I also wanted to do a montly report that
told me who has training that is to expire that month.
 
Well, Let's say I took a class March 1, 2005. You are saying that the
training will expire March 1, 2006, right? If you always require the
training one year later, then you don't need to store a new field for that.
You *know* that it expires one year later, right?

To run a report today, you would simply build a query to pull all records
where the training was done between February 24, 2005 and March 24, 2005.
Correct? Those people are the ones who must retake that class in the next
30 days.

If all that logic is correct, then you really only need two fields in your
table (Name, DateTrained).

To create your report, you must create a new query. Include the two fields
from your table. In the "CRITERIA:" under the DateTrained, you have to put
some kind of criteria to tell it to only pull records where the date is
between February 24, 2005 and March 24, 2005. The most obvious way to do
this would be to put...


Between 02/24/2005 and 03/24/2005

That query could then be used as the record source for your report.

Now, doing it like that means that you would have to go in and revise the
criteria in your query every time you run it. Let's improve on that and use
some variables instead of entering set dates. To do so, change that
"CRITERIA:" entry to the following....


Between DateAdd("m",-12,Date()) And DateAdd("m",-11,Date())


That will give you all the records where the DateTrained is between the
current date minus 12 months and the current date minus 11 months.

Hope that helps.
 
Thank you for your help. I added the line to the date trained criteria but I
don't know how to set up the report. I have tired to make the report to show
that, but the fields come up empty.

Scott
 
I'm sorry, I meant to ask if that criteria will work on Medium date? I set
the Date Trained to be enter as DDMMMYYY.
 
Learning said:
I'm sorry, I meant to ask if that criteria will work on Medium date?
I set the Date Trained to be enter as DDMMMYYY.

Date formatting is merely a display and entry issue. It has zero effect on how
the dates are stored and thus has zero effect on the format you use for
criteria.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top