I have a closed date field and a destruction date field both of which use 
medium date format.  I need the destruction date to be the closed date plus 
ten years. Iam not sure what formula to use to do this.

DateAdd is the function you need.

You may not need to store the destruction date in a table unless the 10-year 
rule is subject to change (in fact you shouldn't unless it's subject to 
change).  You can return the destruction date by including this term in the 
Query Builder:

Destruction-Date:DateAdd("yyyy", 10, [closed_date])
.... which will return the calculated date in a column headed 
"Destruction-Date".

If you haven't come across it, the Expression Builder (look for the magic 
wand labelled "builder...") will repay the effort of learning it.  See:
http://office.microsoft.com/en-us/access/HA102549021033.aspx

If you do need to store the calculated date, you can use an update query to 
update the table with the calculated value.

Phil, London

"WG" <WG@discussions.microsoft.com> wrote in message 
news:3B48F8E9-42D1-4775-8320-5DF61D4D03E2@microsoft.com...
> I have a closed date field and a destruction date field both of which use
> medium date format.  I need the destruction date to be the closed date 
> plus
> ten years. Iam not sure what formula to use to do this. 

In a query, something like below. Make sure that the closed date field name 
is spelt correctly between the [ ] .

Destruction Date: DateAdd("yyyy", 10, [closed date])

However you shouldn't have a destruction date field in your table as you can 
derive the data using something like above. In other words you should 
calculate the destruction date as needed instead of storing it in a table.

Why? What happens if you change the closed date and forget to update the 
destruction date? You will have inconsistant data.  What happens if the 
destruction date rule gets changed to 15 years? You'll need to fix all the 
records already entered.

Here's another reason, but not exactly applicable to your situation. You 
have a Date Of Birth field and an Age field in an Employee table.  After a 
full year the Age field will be wrong. It's better to just drop the Age field 
and calculate the age from the Date Of Birth field as needed.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"WG" wrote:

> I have a closed date field and a destruction date field both of which use 
> medium date format.  I need the destruction date to be the closed date plus 
> ten years. Iam not sure what formula to use to do this.

That is a very thorough answer.  You say I should not store the date in the 
table because it could change.  I would like the date to change.  If a file 
is reopened, then closed again it will have a new closing date.  Will this 
information update with the formula you gave?  Should I store this 
information diffrently?

"Jerry Whittle" wrote:

> In a query, something like below. Make sure that the closed date field name 
> is spelt correctly between the [ ] .
> 
> Destruction Date: DateAdd("yyyy", 10, [closed date])
> 
> However you shouldn't have a destruction date field in your table as you can 
> derive the data using something like above. In other words you should 
> calculate the destruction date as needed instead of storing it in a table.
> 
> Why? What happens if you change the closed date and forget to update the 
> destruction date? You will have inconsistant data.  What happens if the 
> destruction date rule gets changed to 15 years? You'll need to fix all the 
> records already entered.
> 
> Here's another reason, but not exactly applicable to your situation. You 
> have a Date Of Birth field and an Age field in an Employee table.  After a 
> full year the Age field will be wrong. It's better to just drop the Age field 
> and calculate the age from the Date Of Birth field as needed.
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "WG" wrote:
> 
> > I have a closed date field and a destruction date field both of which use 
> > medium date format.  I need the destruction date to be the closed date plus 
> > ten years. Iam not sure what formula to use to do this.

This is precisely why you don't want to store the destruction date. If 
someone changes the closed date or enters in a completely new record directly 
into the table or via a query, you can't get the formula to work. You would 
need to ensure that all records are entered into a form which will use code 
or a macro to update the destruction date when a new record is entered or 
existing record is changed.

Another possibility is to run an update query frequently during the day 
which would update all the destruction dates. However this is also a bad idea 
as there could be a gap between someone using a new record and the update 
query running.

It is almost always a better idea to calculate something like your 
destruction date as needed in a query, form, or report. The exception is when 
you need to store something as it was in a point of time such as an invoice.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"WG" wrote:

> That is a very thorough answer.  You say I should not store the date in the 
> table because it could change.  I would like the date to change.  If a file 
> is reopened, then closed again it will have a new closing date.  Will this 
> information update with the formula you gave?  Should I store this 
> information diffrently?
> 
> "Jerry Whittle" wrote:
> 
> > In a query, something like below. Make sure that the closed date field name 
> > is spelt correctly between the [ ] .
> > 
> > Destruction Date: DateAdd("yyyy", 10, [closed date])
> > 
> > However you shouldn't have a destruction date field in your table as you can 
> > derive the data using something like above. In other words you should 
> > calculate the destruction date as needed instead of storing it in a table.
> > 
> > Why? What happens if you change the closed date and forget to update the 
> > destruction date? You will have inconsistant data.  What happens if the 
> > destruction date rule gets changed to 15 years? You'll need to fix all the 
> > records already entered.
> > 
> > Here's another reason, but not exactly applicable to your situation. You 
> > have a Date Of Birth field and an Age field in an Employee table.  After a 
> > full year the Age field will be wrong. It's better to just drop the Age field 
> > and calculate the age from the Date Of Birth field as needed.
> > -- 
> > Jerry Whittle, Microsoft Access MVP 
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > 
> > 
> > "WG" wrote:
> > 
> > > I have a closed date field and a destruction date field both of which use 
> > > medium date format.  I need the destruction date to be the closed date plus 
> > > ten years. Iam not sure what formula to use to do this.

Yes, the destruction date will be recalculated by the query when the closing 
date is changed.  The destruction date is NOT stored, but is always 
calculated. See Jerry Whittle's paragraph below.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

WG wrote:
> That is a very thorough answer.  You say I should not store the date in the 
> table because it could change.  I would like the date to change.  If a file 
> is reopened, then closed again it will have a new closing date.  Will this 
> information update with the formula you gave?  Should I store this 
> information diffrently?
> 
> "Jerry Whittle" wrote:

>> Why? What happens if you change the closed date and forget to update the 
>> destruction date? You will have inconsistant data.  What happens if the 
>> destruction date rule gets changed to 15 years? You'll need to fix all the 
>> records already entered.