?
Solved

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

Posted on 2010-11-15
14
Medium Priority
?
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 51

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 1000 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 51

Accepted Solution

by:
Gustav Brock earned 1000 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 51

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 51

Expert Comment

by:Gustav Brock
ID: 34144372
You are welcome!

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

777 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