You are here:

Using MS Access/Future date multipication

Advertisement


Question
QUESTION: I have two fields: 1.  Date of Birth (01-01-1990) (MM-DD-YYYY) and 2.  Retirement Age (ie. 65)  in MS Access.  I would like to find out the date of retirement that is 1-1-1990+65 = 31-12-2054 but how it will be find out through programming/query in a new filed of date of retirement?  Please tell me.  Thanks.

ANSWER: First, there is no need for storing this in a field. As a general rule we do not store calculated values.

If your equation is simply DOB+65, then you can use the DateAdd function to display this date

DateAdd("yyyy",65,[DOB])

If you need it to be the day before, just add -1.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: For calculating one date this will work but I have a big data sheet of about 500 employees of several category where retirement age is different for each category, such as 60, 62 and 65 and I would like to find out the 'Date of Retirement' in a new field by multiplying 60, 62 and 65 into 'Date of Birth' of each staff.  Please help me.  Thanks and with Regards.

Answer
Again, you should NOT be storing this value. If the retirement age is different for each category, I assume there is a field that indicates the category. That field should be joined back to a lookup table of categories. In the lookup table, you should indicate the number of years for each category. Then plug that number into your DateAdd expression.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.