How can I set up this date field?

[tep186]

I have two date fields, term date and destroy date. I need the destroy date to 5 years after the term date. For example, the term date is 1/16/2005, the destroy date should be 1/16/2010. Can I use a default value or do I have to actually code it in vba?

[JoeM]

If the Destroy Date follows a "hard-and-fast" rule like that where it is directly dependent upon the value of another field, it shouldn't really be stored anywhere. It is just a calculation somewhere (can be done in a query or VBA).

[Z1nkstar]

Here is my conclusion I have done some testing with this and I have the code I used here I added it to the After Update Event.

Private Sub TermDate_AfterUpdate()


DestroyDate = DateAdd("yyyy", 5, TermDate)


End Sub

The only issue I have with this code is that to get the Destroy date to show up all you have to do is click the field.
You could set a refresh button on the form that will also remind you that you have to refresh that one field so it shows up.

[JoeM]

deleted (re-posted with more details)

[JoeM]

The issue with using an actual field to store the value is that it is not necessarily dynamic and could lead to data integrity issues. Using an AfterUpdate event only affects records update in a Form. If the TermDate field were updated in the Table directly, or in a Query, the VBA code would not run. So you could end up with incomplete or incorrect values. It actually violates one of the 3rd Rules of Normalization, which says that "Third normal form requires that not only every non-key column be dependent on the entire primary key, but that non-key columns be independent of each other" (see: http://office.microsoft.com/en-us/ac...010341617.aspx). In a nutshell, you should never store that which can be calculated.

If you calculate it as a calculated field in a query, it will always be correct, as it is calculated dynamically based on whatever value is in the other field. The calculation in a query looks very similar, i.e.

DestroyDate: DateAdd("yyyy",5,[TermDate])

[tep186]

Is there anyway to do it if I only need the year. For example 1/1/2012 would just 2017.

I figure it out.. Thanks for your help guys

[Z1nkstar]

This is what you need to do
-Open the Table
-Select Date Field
-Under Format at Bottom Of Screen
-Type yyyy

See Attached Picture