calulate backwards

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

Guest

I need to create a query base on the expiration date of a drivers license.
It must notifiy me every 2 years to the expiration date and the fact it has
expired after the license expiration date that it has to be reviewed or
updated. Case in point [DriverLicensNo] 123456 with an expiration date
[DriverExpire] of 03/26/2012 should notify me in 2006, 2008, 2010 and 2012
of the same month that it has to be review and has completly expired after
the 2012 date.
 
Dear Nick:

Here's a starter. Use this:

? DateDiff("m", "3/28/2006", "1/1/2009") MOD 24

Put your own dates in for the date today and the date of the license expiry.
It divides by 24 (2 years in months). The result will be zero when the
reminder is needed.

Does this help?

Tom Ellison
 
As you stated it is a start and I do get the "0's" for those years that need
to be update but how do I get the query to tell me all dates for this year
alone?
Thanks

Tom Ellison said:
Dear Nick:

Here's a starter. Use this:

? DateDiff("m", "3/28/2006", "1/1/2009") MOD 24

Put your own dates in for the date today and the date of the license expiry.
It divides by 24 (2 years in months). The result will be zero when the
reminder is needed.

Does this help?

Tom Ellison



Nick said:
I need to create a query base on the expiration date of a drivers license.
It must notifiy me every 2 years to the expiration date and the fact it
has
expired after the license expiration date that it has to be reviewed or
updated. Case in point [DriverLicensNo] 123456 with an expiration date
[DriverExpire] of 03/26/2012 should notify me in 2006, 2008, 2010 and
2012
of the same month that it has to be review and has completly expired after
the 2012 date.
Click to expand...
Click to expand...
 
To get the repeating values for the years ahead, create a table with just
one field of type Number. Call the field (say) CountID. Mark it as primary
key. Save the table as tblCount. Enter records for the values 2, 4, 6, 8,
....

Now create a query that has your existing table, and tblCount. There should
be no line joining the 2 tables in the upper pane of the query design
window. This gives every possible combination (called a Cartesian Product.)

In the Field row, enter:
AdvanceWarning: DateAdd("yyyy", -tblCount.CountID, [DriverExpire])
Add whatever criteria you want. For example, the current month would be:
Between DateSerial(Year(Date()), Month(Date()),1) And
DateSerial(Year(Date()), Month(Date())+1,0)
 
Dear Nick:

What do you mean?

Example might help.

Tom Ellison


Nick said:
As you stated it is a start and I do get the "0's" for those years that
need
to be update but how do I get the query to tell me all dates for this year
alone?
Thanks

Tom Ellison said:
Dear Nick:

Here's a starter. Use this:

? DateDiff("m", "3/28/2006", "1/1/2009") MOD 24

Put your own dates in for the date today and the date of the license
expiry.
It divides by 24 (2 years in months). The result will be zero when the
reminder is needed.

Does this help?

Tom Ellison



Nick said:
I need to create a query base on the expiration date of a drivers
license.
It must notifiy me every 2 years to the expiration date and the fact it
has
expired after the license expiration date that it has to be reviewed
or
updated. Case in point [DriverLicensNo] 123456 with an expiration date
[DriverExpire] of 03/26/2012 should notify me in 2006, 2008, 2010 and
2012
of the same month that it has to be review and has completly expired
after
the 2012 date.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top