Calculating remaining dates

Posted on 2012-08-30
Last Modified: 2012-08-31

I have a table containing details of employees together with their date of birth.  I would like to be able to display the number of days remaining until their birthday.  I would like to display this as an additional field on the form.

I am using Access 2010

Any help would be appreciated

Question by:Morpheus7
    LVL 77

    Assisted Solution

    set the controlsource of a textbox to
    =iif(dateserial(year(Date()), Month(DOB), day(DOB))>Date(),  dateserial(year(Date()), Month(DOB), day(DOB))-Date(), dateserial(year(Date())+1, Month(DOB), day(DOB))-Date())

    Obviously use your own field name for DOB
    LVL 39

    Accepted Solution

    This is similar approach, but with function:
    Public Function DaysUntilBirth(DOB As Date) As Integer
    Dim Dt As Date
    Dt = DateSerial(Year(Date), Month(DOB), Day(DOB))
    If Dt < Date Then Dt = DateAdd("yyyy", 1, Dt)
    DaysUntilBirth = DateDiff("d", Date, Dt)
    End Function

    Open in new window

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    <No Points wanted>

    ...or your either technique and use it in a query.
    Then this new "field" will be available in any Form or Report you create from the query...


    LVL 48

    Assisted Solution

    by:Gustav Brock
    If you need an exact count also for/in leap years and for leaplings (those born on 29th Feb.), you first have to find the exact age using the function below:

    Age: AgeSimple([Date of Birth])

    Then add this to the birth date to find the next anniversary date:

    AnniversaryDateNext: DateAdd("yyyy", 1 + AgeSimple([Date of Birth]), [Date of Birth])

    Now, using and combining this, the count of days until the next anniversary date can be found using this expression for your textbox on the form:

    =DateDiff("d",Date(),DateAdd("yyyy", 1 + AgeSimple([Date of Birth]), [Date of Birth]))

    Of course, replace [Date of Birth] with the actual name of your field.
    Public Function AgeSimple( _
      ByVal datDateOfBirth As Date) _
      As Integer
    ' Returns the difference in full years from datDateOfBirth to current date.
    ' Calculates correctly for:
    '   leap years
    '   dates of 29. February
    '   date/time values with embedded time values
    ' DateAdd() is used for check for month end of February as it correctly
    ' returns Feb. 28. when adding a count of years to dates of Feb. 29.
    ' when the resulting year is a common year.
    ' After an idea of Markus G. Fischer.
    ' 2007-06-26. Cactus Data ApS, CPH.
      Dim datToday  As Date
      Dim intAge    As Integer
      Dim intYears  As Integer
      datToday = Date 'datDateCurrent ' Date
      ' Find difference in calendar years.
      intYears = DateDiff("yyyy", datDateOfBirth, datToday)
      If intYears > 0 Then
        ' Decrease by 1 if current date is earlier than birthday of current year
        ' using DateDiff to ignore a time portion of datDateOfBirth.
        intAge = intYears - Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, datDateOfBirth)) > 0)
      End If
      AgeSimple = intAge
    End Function

    Open in new window


    Author Closing Comment

    Many thanks to all
    LVL 48

    Expert Comment

    by:Gustav Brock
    You are welcome!


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    733 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

    24 Experts available now in Live!

    Get 1:1 Help Now