the number of years left query

[claine]

Hello

I have a submission date field and a no of years field. I want to create a query that returns a number of years but subtract the years that have already been:

For example submission date = 28/11/2011 + no of years = 30, which works out to be 28/11/2041 but I also want the return to subtract the submission years already been (2013 - 2011) = 2

Is there a criteria query to calculate the no of years minus the years already been from the submission date?

Charlotte

 

[pr2-eugin]

Lookup on DateDiff function.. I think that is what you are after..

 

[claine]

Thank you pr2-eugin

I have manage to somewhat achieve what I want using
Yrs left on Mortgage: [Years]-DateDiff("yyyy",[Submission Date],Date()) however, the query only returns the no of years back to 2011, and won't calculate any further than 2 years difference.
Any ideas?
Charlotte

 

[pr2-eugin]

Could you show some sample data in the table? with the desired outcome? Or better upload a Stripped DB.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[claine]

Hi pr2-eugin

Thank you for your help. I have figured my problem out using the same criteria I have mentioned above in another query. then using that query to link the results to the other queries.

Charlotte

 

[Brianwarnock]

Charlotte

I don't know how accurately you want the result but be aware that Datediff merely subtracts the years in the date portion of the dates so 31/December/2012 to 1/January/2013 is considered 1 year.

You may want to consider working in days and dividing by 365 as a rough but more accurate measure, you could then justt take the Int of the result. It all depends what you actually want.

Brian