Calculating date/time remaining in a table

[stuartkeith]

Hi, I have never had to do any maths with access before, so I don’t know where to start!

What I need to do is calculate remaining warranty information for products.

In the table I have 3 columns. the 1st column is "Purchase date (dd/mm/yy)" 2nd column is "Total warranty period (years)" and the 3rd column is "Remaining warranty"

The 3rd column is the one that will display how long is left to run! How do I achieve this?

Thanks.

 

[ColinEssex]

You don't need to save the 3rd column as it changes daily. You can calculate it on the fly when you need it. Several ways of doing it, one way is

Do a DateAdd() to get the warranty end date then

Do a DateDiff() between Today and the warrenty end date

Col

 

[stuartkeith]

Ok ive have changed the ideas slighty now its on the form and the expirey is now done as a date so it will say warrenty expiry "dd/mm/yy" so I know I need to tell it to add 2yrs onto the year field!

I will play around with what you said too, thanks!

 

[KeithG]

Its never a good idea to store a value you can calculate in a field because you can always just re-calculate it.

 

[stuartkeith]

great the date add thing works, however it also adds x number to both days and years?

so if I say the wrnt period is 2 years and purchase date was 12/01/2007 then the expirey would give me 14/01/2009

Have I done somthing wrong?

this is the code i have used!

remaining.Value = cmb_wrnt.Value + DateAdd("yyyy", cmb_wrnt.Value, txt_purchasedate.Value)

 

[boblarson]

Format the dates to U.S. style mm/dd/yyyy to do the calculations and then format them back to your own needs.

 

[stuartkeith]

ok sorted thanks, just removed the 1st cmb_value