Calculated Date Field

[RBolinsky]

Trying to find the easiest and efficient way to add 5 years to a predetermined date.



I have a field in a table (tblAssets) titled Purchased_Date. I want to simply add 5 years to that date and then store it in Replacement_Date. Both of those fields are in that same table (tblAssets).


Expected result is: 1/1/2008 would equal 1/1/2013. I can get the calculation correct by using an expression in a select query. However, i need it to store those values and that is where i keep jacking it up. Something simple im sure.

[kd2017]

So you need to store a calculated value? That's usually a big no no.

Are you doing this from a form or query? To all record or just a few? How are you deciding which to update?

From the context of your field names maybe it would be appropriate to have a purchase date and "shelf life" field and simply calculate your replacement date on the fly with queries.

[armyofdux]

So you need to store a calculated value? That's usually a big no no.

Are you doing this from a form or query? To all record or just a few? How are you deciding which to update?

From the context of your field names maybe it would be appropriate to have a purchase date and "shelf life" field and simply calculate your replacement date on the fly with queries.

Calculating on the fly with a query would be ideal.

[armyofdux]

And from a form

[kd2017]

A query to add five years on the fly to all the rows in your tblAssets might look like this:

SELECT *, DateSerial(Year(Purchased_Date) + 5, Month(Purchased_Date), Day(Purchased_Date)) AS Replacement_Date FROM tblAssets

Let's say you had a field in the table that provides the expected life span of the asset, then some things might be 3 years, others might be 5 years, and you can still query the replacment date in one query. Assuming this field is called LifeSpan and is a number data type in years:

SELECT *, DateSerial(Year(Purchased_Date) + LifeSpan, Month(Purchased_Date), Day(Purchased_Date)) AS Replacement_Date FROM tblAssets

[RBolinsky]

Oh i see what you mean with the lifespan. That would be a variable that could also be entered. Great idea. For now I think the first will work. It's a new every 5 type of arrangement. Thanks!

[RBolinsky]

So I am trying to do the second option you gave me.

I was incorrect in some of my naming conventions.

The table name: Assets

The field with the purchased date is titled: PurchasedDate (Data type is Date)

The field I need to update is titled: RetiredDate (Data type is Date)

I added a field: Lifespan (data type is number)

I modified the select statement:

SELECT *, DateSerial(Year(PurchasedDate) + LifeSpan, Month(PurchasedDate), Day(PurchasedDate)) AS RetiredDate FROM Assets

However it doesnt seem to do anything... should this be an update query or select? Trying to enable this on a form.

[kd2017]

The examples I gave weren't meant to be saved to the database. As I mentioned the in a previous reply that's saving a calculated value which is usually a bad idea. That said, only because you asked, even though it's probably a bad idea, what you're looking for is an update query, not a select query.

UPDATE Assets SET RetiredDate=DateSerial(Year(PurchasedDate)+5, Month(PurchasedDate)), Day(PurchasedDate))

FYI that has no where clause and will edit every row in the table. And if you're doing this there is no point in the life span field.