Solved

Add a number of years onto a date field in MS Access

Posted on 2010-11-15
14
290 Views
Last Modified: 2013-11-28
I have one field called DateOfIssue (Date Field - i.e. 1 January 2001)and one called Expiry (which is a number field i.e. 3) which is the number of years until this certificate expires.  I need to add the Expiry onto the Date of Issue and come up with an Expiry date.

I am sure I have done this before but after searching my knowledgebase and then Experts Exchange I cannot find exactly what I need.

Please can somebody help me

0
Comment
Question by:AnnetteDavid
  • 4
  • 3
  • 2
  • +3
14 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34135867
0
 
LVL 14

Expert Comment

by:leoahmad
ID: 34135876
use dateadd function like



    DateAdd ("yyyy", 3, #22/11/2003#)       would return '22/11/2006'
0
 
LVL 14

Expert Comment

by:leoahmad
ID: 34135890
In Access, the DateAdd function returns a date after which a certain time/date interval has been added.

The syntax for the DateAdd function is:

    DateAdd ( interval, number, date )

interval is the time/date interval that you wish to add. It can be one of the following values:

    Value       Explanation
    yyyy       Year
    q       Quarter
    m       Month
    y       Day of the year
    d       Day
    w       Weekday
    ww       Week
    h       Hour
    n       Minute
    s       Second

number is the number of intervals that you wish to add.

date is the date to which the interval should be added.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 18

Expert Comment

by:Cluskitt
ID: 34135897
Use VBA:

NewDate=Dateadd("y", Cells(1, 2).Value, Cells(1, 1).Value)

This reads the date in A1 and adds the number of years in A2. The syntax is: Dateadd(format, number, date).

If you want to use it as a function, create a module and insert this:

Public Function AddDate(OldDate As Date, AddNumber As Integer)
  AddDate=Dateadd("y", AddNumber, OldDate)
End Function


Now you can call it in a cell using:
=AddDate(A1, A2)
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34135964
The thread below is similar with your issue. You may get an idea from it.

http://www.eggheadcafe.com/forumarchives/Accessdevtoolkits/Mar2006/post26014264.asp

Sincerely,
Ed
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 34135989
Wow, only now did I notice that I read the question wrong. That is, I read it right, I just thought, for some reason, that it was for Excel. :P

As said before, Dateadd is the way to go: Dateadd("y", NumberField, DateField).
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34136118
Set the ControlSource of the TextBox in your report to:

=DateAdd("yyyy",[Expiry],[DateOfIssue])

/gustav
0
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 250 total points
ID: 34136320
To apply the one I posted at  ID: 34135964 in your situation you may try the suggestion below:

To add [Expiry]  to [DateOfIssue] in your query, add the following to an empty cell on the Fields line:  
 ExpiryDate: DateAdd("yyyy",[Expiry], [DateOfIssue])  

Sincerely,
Ed
0
 

Author Comment

by:AnnetteDavid
ID: 34144176
I am sorry folks I am really feeling quite stupid here but I cannot make it work

My fields are called

ExpiryDate
NoYearsTillExpiry
DateOfIssue

I have the following

DateAdd("yyyy" (this is where I think I am going wrong),[NoYearsTillExpiry],[DateOfIssue]

And I am getting nowhere, I really appreciate your help though
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 34144203
It's just "y", not "yyyy". "y" defines the measure to be calculated, in this case years, not format. If you want to format, you have to Format("yyyy",Dateadd("y",[NoYearsTillExpiry],[DateOfIssue]))
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 34144227
Well, set the ControlSource of the TextBox in your report to:

=DateAdd("yyyy",[NoYearsTillExpiry],[DateOfIssue])

You will not need (to store) the ExpiryDate as that date will be calculated on the fly.

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34144249
Too much confusion here ... "y" will add a count of days only.

You have to use "yyyy" which adds a count of years.

Format should not be used in the expression. If you need a specific format of your expiry date, apply that format to the TextBox in your report.

/gustav
0
 

Author Closing Comment

by:AnnetteDavid
ID: 34144339

Thanks to everyone for your help
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34144372
You are welcome!

/gustav
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question