• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

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

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
AnnetteDavid
Asked:
AnnetteDavid
  • 4
  • 3
  • 2
  • +3
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
use dateadd function like



    DateAdd ("yyyy", 3, #22/11/2003#)       would return '22/11/2006'
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CluskittCommented:
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
 
MINDSUPERBCommented:
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
 
CluskittCommented:
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
 
Gustav BrockCIOCommented:
Set the ControlSource of the TextBox in your report to:

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

/gustav
0
 
MINDSUPERBCommented:
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
 
AnnetteDavidAuthor Commented:
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
 
CluskittCommented:
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
 
Gustav BrockCIOCommented:
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
 
Gustav BrockCIOCommented:
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
 
AnnetteDavidAuthor Commented:

Thanks to everyone for your help
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now