Renewing

[desireemm]

I have a form that has a subform that has dates in each field each date is in correlation to the Final Date if the final date says 1/31/2000 then the renewal dates should say 1/31/2002 then the second one should say 1/31/2004. What this means is that an employee must renew every 2 years. what I am trying to do is make all the renewal dates automatically populate once the final date has ben input to the final field, and the drop down field that is next to the renwal fields to automatically say yes or no depening on if that date has passed. Does that make sense??? Can someone explain to me how to do this??


Thank you

 

[RichO]

If your subform is a continuous form, you would need separate fields in your table for the renewal date and the yes/no field. Maybe you already have it set up the right way.

In the After Update event of the Final Date field on your subform, try:

RenewalDate = DateAdd("y", 2, FinalDate)
If RenewalDate > Date then DropDownField = "YES"
Me.Requery

 

[desireemm]

Renewals

I am aware that I should not have more than one date, repeating values rule I think (validates database rules) but please understand I am still learning about databases and I didnt really know how to create the query for the renewal dates. I did some reasearch and I ws thinking i could just have the final date and the firstrenewal date in the query have this dateserial code
=DateSerial(Year([FIRSTRENEWALDATE])+2,Month([FIRSTRENEWALDATE]),Day([FIRSTRENEWALDATE])). I was thinking of setting this up in a query. then placing it in my subform which is continous. Thank you for the replies

 

[desireemm]

Don't use that calculation, use the DateAdd() function. It will properly take care of the variable days in february should you add 1 year to a Feb 29 date. Rich already showed you the function's syntax.

Thank you the only thing is the renewal dates are suppose to be always at the end of the month and the year is should be 2yrs from the final date. it should look like this

Final= 2/02/2000
Renewal = 02/28/2002
secondrenewal= 02/28/2004
etc...


Thats what I am trying to have the database do automatically so far the only thihng I have gotten to do that exactly is the dateserial function...Help?


Please.....

 

[desireemm]

- your DateSerial() function isn't changing Feb 2 to Feb 28. If you want a function that returns the last day of a given month, download the useful date functions db from the samples section.

Thank you Pat I really appreciate your help