# Thread: Trouble with date calculation

1. Registered User
Join Date
Apr 2011
Posts
12

## Unanswered: Trouble with date calculation

I seem to be having difficulty calculating dates. I have a "date of birth" column with a "date/time" data type which displays a "medium date" (i.e. 19-Jun-07).

I have registration form for some programs and what I would like to do is calculate the age of the registrant for 1 Sep 11. If they are over 18 at this date, then they must participate in the adult program. What is the syntax to return the age for a projected date? The now() function does not seem to allow for those who have birthdays between todays date and the projected date.

Thanks

2. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
Don't know what you want to do if the age on 1-Sep-2011 is over 18, but the following code will return the age on this date, into the unbound control named Age_1_9_2011, based on a 'date of birth' Control named DOB, and can easily be modified to do whatever if the returned age is over 18.

Code:
```Private Sub DOB_AfterUpdate()
If Nz(Me.DOB, "") <> "" Then
Me.Age_1_9_2011 = DateDiff("yyyy", [DOB], #1/9/2011#) - IIf(Format\$(#1/9/2011#, "mmdd") < Format\$([DOB], "mmdd"), 1, 0)
End If
End Sub```
Code:
```Private Sub Form_Current()
If Nz(Me.DOB, "") <> "" Then
Me.Age_1_9_2011 = DateDiff("yyyy", [DOB], #1/9/2011#) - IIf(Format\$(#1/9/2011#, "mmdd") < Format\$([DOB], "mmdd"), 1, 0)
End If
End Sub```
BTW, Now() returns Date and Time, while Date() returns the Date only. Now() should only be used if the Time component is absolutely necessary.

If you wanted an unbound Control to reflect the current age anytime you accessed a record, in the code above you would simply replace

#1/9/2011#

with

Date.

Linq ;0)>

3. Registered User
Join Date
Apr 2011
Posts
12

## Solution worked just fine

Thanks - it did the trick. I also found another post where the user had similar problems and he utilized this solution which I incorporated
Code:
`DateDiff("yyyy", [Date_of_Birth], DateSerial(Year(Date), 9, 1))`

4. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
Not sure what you mean by 'incorporate,' but if you mean you're actually using this function, as you just gave it, for your application, you'll find it will not always accurately return the age on September 1! It will only give an accurate age for people whose birthdays occur on or after 1 September.

DateDiff() using "yyyy" as the interval only calculates the difference in years! In other words,

DateDiff("yyyy",#12/31/2011#, #1/1/2012#)

will return 1 year as the difference between 12/31/2011 and 1/1/2012, even though the two dates are only 1 day apart, because it simply uses the year component and calculates the difference between 2012 and 2011.

Now you can replace

#1/9/2011#

in the code I gave you with

DateSerial(Year(Date), 9, 1)

to make it calculate the age on September 1 of whatever the current year is, so you don't have to re-code it every year. If that's what you meant by 'incorporate' that's fine.

Linq ;0)>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•