Solved

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

Posted on 2010-11-15
14
296 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
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!

 
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 50

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 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 34144372
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!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

691 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