Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / February 2006

Tip: Looking for answers? Try searching our database.

What criteria for a date to expire?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Learning Access - 24 Feb 2006 18:32 GMT
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
Rick B - 24 Feb 2006 19:14 GMT
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.

Signature

Rick B

>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
[quoted text clipped - 14 lines]
>
> Scott
Learning Access - 24 Feb 2006 19:29 GMT
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, 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
[quoted text clipped - 21 lines]
> >
> > Scott
Rick B - 24 Feb 2006 21:19 GMT
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.

Signature

Rick B

>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
[quoted text clipped - 33 lines]
>> >
>> > Scott
Learning Access - 25 Feb 2006 07:54 GMT
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

> 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
[quoted text clipped - 68 lines]
> >> >
> >> > Scott
Learning Access - 25 Feb 2006 07:55 GMT
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.

> 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
[quoted text clipped - 68 lines]
> >> >
> >> > Scott
Rick Brandt - 25 Feb 2006 13:05 GMT
> 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.

Signature

I don't check the Email account attached
to this message.     Send instead to...
RBrandt    at       Hunter      dot      com

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.