You are here:

# Using MS Access/Future date multipication

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

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.

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
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Thank you very much for your kind support.

Using MS Access

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