Calculating a future date

  • Thread starter Thread starter WG
  • Start date Start date
W

WG

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
 
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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top