How to calculate a date in a form

[scottappleford]

Hi

I have a form and on it I have two date fields, what I would like to do is enter one dat in one of the fields then have the other field automatically update to five years from that date.

I would then like a message box to tell me when the date that is five years later has passed and I can get rid of the boxes I have in storage.

Sorry I have an archive db and the boxes archived can be disposed of after five years.

Thanks

scott

 

[Mile-O]

I have a form and on it I have two date fields, what I would like to do is enter one date in one of the fields then have the other field automatically update to five years from that date.

In the AfterUpdate() event of your first date put the following code:

Me.txtYourEndDate = DateAdd("yyyy", 5, Me.txtYourStartDate)

I would then like a message box to tell me when the date that is five years later has passed and I can get rid of the boxes I have in storage.

You can use an Delete Query to remove all of the records over 5 years (based on the start date) by putting the criteria as:

< DateAdd("yyyy", -5, Date())


There's no need to store the 5 years later date as it can be calculated anytime from the initial date.

 

[scottappleford]

A bit unsure

Hi thanks for the reply.

I do not want to delete the records, I just need the db to tell when the date has passed?

Thanks.

 

[Mile-O]

Then why not just use a SELECT query using the criteria I stated to return all cases that are over the time period?

And if you are doing it on a form for an individual box's case then, in the form's OnLoad() event you could determine if the five year + date is less than today then use the MsgBox command to display your message.