Calculating Date

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

Guest

Hi there,

I have a query that pulls information on contracts that run in annual
cycles. Each record for each contract has a "renewal_date" and based on this
date I use the DateAdd Function to determine an earlier date by which a
renewal notice has to be sent out.

Here's the problem: while the month and the day for each renewal_date is
correct, the year may be wrong (don't ask--I inherited this project...). So
is there some kind of calculation I can run in the field cell of the query
that will return a new version of "renewal_date"--in mm/dd/yyyy format--where
the month and date are the same but the year represents the next re-up date
for the contract?

(I hope that made sense).

Thanks,
YYY
 
Hi there,

I have a query that pulls information on contracts that run in annual
cycles. Each record for each contract has a "renewal_date" and based on this
date I use the DateAdd Function to determine an earlier date by which a
renewal notice has to be sent out.

Here's the problem: while the month and the day for each renewal_date is
correct, the year may be wrong (don't ask--I inherited this project...). So
is there some kind of calculation I can run in the field cell of the query
that will return a new version of "renewal_date"--in mm/dd/yyyy format--where
the month and date are the same but the year represents the next re-up date
for the contract?

(I hope that made sense).

Thanks,
YYY
Click to expand...

Well, if renewals are due, let's say on March 25, and are renewed
annually, then the year doesn't matter.
If you wish to send a renewal letter out for those records one month
earlier (i.e. 2/25) , then on that date, filter for renewals that are
due March 25.

Where Format([DateField],"mm/dd") = "03/25"

Better yet, have the query prompt for the wanted month and date.
Where Format([DateField],"mm/dd") = [Enter the month and date mm/dd]

When prompted, enter 03/25 and all records due that date will be
returned, regardless of the year in the field.
 
Hi there,

I have a query that pulls information on contracts that run in annual
cycles. Each record for each contract has a "renewal_date" and based on this
date I use the DateAdd Function to determine an earlier date by which a
renewal notice has to be sent out.

Here's the problem: while the month and the day for each renewal_date is
correct, the year may be wrong (don't ask--I inherited this project...). So
is there some kind of calculation I can run in the field cell of the query
that will return a new version of "renewal_date"--in mm/dd/yyyy format--where
the month and date are the same but the year represents the next re-up date
for the contract?

(I hope that made sense).

Thanks,
YYY
Click to expand...

Take a look at the DateSerial function:

DateSerial(<the right year>, Month([renewal_date]),
Day([renewal_date])

It's not clear from your post how the year of the next re-up date
should be calculated.

John W. Vinson [MVP]
 
Back
Top