Calculating Dates of Birth in Years??? Help! 2

[SherryLynn]

I have a database which tracks dates of birth of different individuals. I have a report which is pulling statistical information based on a query of 4 tables in the database. In the report I need to break down age groups for the dates of birth. For example one section has: Under 1 year, 1-3 years, 4-10 years, 11-15 years, 16 and over. How can I write an expression or code or whatever which will take the date of birth values, reveal the number in years and calculate how many of those individuals fall into that particular category? Any help is much appreciated.

Thanks,
Sherry

 

[JerryDennison]

This can be done in a two step process.

Step One: Create a function to provide your groupings

Public Function AgeGroup(datDOB As Date) As String

Dim intAge As Integer

intAge = DateDiff(&quot;y&quot;, Now()) + (Date < DateSerial(Year(Now()), Month(datDOB), Day(datDOB)))

Select Case intAge

Case < 1
AgeGroup = &quot;Under 1&quot;

Case Between 1 And 3
AgeGroup = &quot;1-3 years&quot;

Case Between 4 And 10
AgeGroup = &quot;4-10 years&quot;

Case Between 11 And 15
AgeGroup = &quot;11-15 years&quot;

Case > 16
AgeGroup = &quot;over 16 years&quot;

End Select

End Function

Step two: Create a totals query where you Group By the function and Count the PK.

AgeGrouping: AgeGroup([DOB])

 

[LarryDeLaruelle]

Sherry:

Here's another function. This will give the age as years and months.

Public Function AgeSet(DOB As Date)
Dim strYears As String
Dim strMonths As String
Dim datCurrent As Date

datCurrent = Date

If DOB > Date Then Exit Function

'Scenario 1: Birth month is earlier than the Current Month - Year Anniversary has occurred
' Age is Years equals the Current Year minus the Birth Year
' AND
' Birth Day is equal to or earlier than the Current Day - Month Anniversary has occured
' Number of months since last Birthday equals the Current Month minus the Birth Month
' OR
' Birth Day is after the Current Day Month Anniversary has not occured
' Number of months since last Birthday equals the previous month minus the Birth Month
' (Do not include current month since birthday has not occurred)

If Month(DOB) < Month(datCurrent) Then
strYears = Year(datCurrent) - Year(DOB)
If Day(DOB) <= Day(datCurrent) Then
strMonths = Month(datCurrent) - Month(DOB)
Else
strMonths = (Month(datCurrent) - 1) - Month(DOB)
End If

'Scenario 2: Birth Month is after the Current Month - Year Anniversary has not occurred
' Age in Years equals the Current Year minus the Birth Year - 1
' (Do not include current year since birthday has not occurred)
' AND
' Birth Day is equal to or earlier than the Current Day - Month Anniversary has occurred
' Number of months since last Birthday equal the Current Month Plus 12 minus the Birth Month
' OR
' Birthday is after the Current Day - Month Anniversar has not occured
' Number of months since last Birthday equals the Current Month + 11 minus the Birth Month

ElseIf Month(DOB) > Month(datCurrent) Then
strYears = Year(datCurrent) - Year(DOB) - 1
If Day(DOB) <= Day(datCurrent) Then
strMonths = (Month(datCurrent) + 12) - Month(DOB)
Else
strMonths = (Month(datCurrent) + 11) - Month(DOB)
End If

'Scenario 3: Birth Month is equal to the Current Month - Year Anniversay is in the Current Month
' AND
' Birth Day is equal to or earlier then the Current Day - Birthday has occurred
' Age in Years equal the Current Year minus the Birth Year and Months = 0
' OR
' Birth Day is after the Current Day - Year Anniversary has not occurred
' Age in Years equals the Current Year - Birth Year -1 and Months = 11
Else
If Day(DOB) <= Day(datCurrent) Then
strYears = Year(datCurrent) - Year(DOB)
strMonths = 0
Else
strYears = Year(datCurrent) - Year(DOB) - 1
strMonths = 11
End If
End If

AgeSet = strYears & &quot; Years and &quot; & strMonths & &quot; Months&quot;

End Function

Larry De Laruelle
larry1de@yahoo.com

 

[LonnieJohnson]

There is a really cool website called Access Web and it has a short expression for calcing ages and working with dates.

http://www.mvps.org/access/datetime/index.htm

ljprodev@yahoo.com
ProDev
MS Access Applications

 

[SherryLynn]

Hi Jerry,

I have no real experience with VBA so I need a little direction to be able to use the solution you provided me. Where exactly should I add this code? Should I have the report open in design mode and click on the build button (this takes me to &quot;General&quot; in the vba window)? When I type the code in there (and I'm probably in the wrong spot), I get some odd issues. First, when I type Case < 1 it changes Case to Case Is and it won't let me change it back. The second issue, for each of the lines regarding case (ie. Case Between 1 And 3) I get an error message - &quot;Compile Error - Expected: End of Statement&quot; and it then highlights the line in red. Help!

Hi Larry,

As above, I don't really have experience with VBA and so again, I'm not sure where I put the code, etc. Can you help? Thanks.

Hi Lonnie,

I went to that web site and checked out the expression you mentioned. Where would I enter that expression? Would it be in a text box on the report? In the query? Do you happen to know? Thanks.

Sherry

 

[JerryDennison]

Sorry about that, try this instead(a little syntax problem on my part, brain freeze):

Public Function AgeGroup(datDOB As Date) As String

Dim intAge As Integer

intAge = DateDiff(&quot;y&quot;, Now()) + (Date < DateSerial(Year(Now()), Month(datDOB), Day(datDOB)))

Select Case intAge

Case Is < 1
AgeGroup = &quot;Under 1&quot;

Case 1 To 3
AgeGroup = &quot;1-3 years&quot;

Case 4 To 10
AgeGroup = &quot;4-10 years&quot;

Case 11 To 15
AgeGroup = &quot;11-15 years&quot;

Case Is > 16
AgeGroup = &quot;over 16 years&quot;

End Select

End Function

Copy and paste this function into a module. You then call the function as you would any built-in function:

=GroupAge([DOB])

 

[SherryLynn]

Hi Jerry,

Ok, I wrote the code into a module. Now I'm not sure where I need to call the function. I tried putting it on the form where the DOB field shows, but I get a Compile Error - Argument not optional (Error 449). It then takes me to the module window and highlights the DateDiff part of the code. I'm really new to this and am not sure how to create the query you mentioned (totals query) and where I would add the function there. Your help is much appreciated.

Thanks, Sherry

 

[JerryDennison]

You must pass a valid date field to the function. Use the function anywhere you'd like. The syntax is:

=AgeGroup([DOBFieldName])