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

Calculating remaining dates


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

3 Solutions
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
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

Jeffrey CoachmanMIS LiasonCommented:
<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...


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
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

Morpheus7Author Commented:
Many thanks to all
Gustav BrockCIOCommented:
You are welcome!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now