Solved

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

Posted on 2010-11-15
14
285 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
Comment Utility
0
 
LVL 14

Expert Comment

by:leoahmad
Comment Utility
use dateadd function like



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

Expert Comment

by:leoahmad
Comment Utility
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
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Set the ControlSource of the TextBox in your report to:

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

/gustav
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

Thanks to everyone for your help
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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.​
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now