# Need reading age in years and months -MS Access

jhm 15:07 09 Jul 2004
Locked

I am compiling a database for my school-teacher daughter. She wants a field to be the actual age of the child using DOB and current date. Can do expression easy, but the age comes out as a decimel of a year and she needs it to be yy.mm

Any one know if poss from Access or will it involve multiple math formulaes

Scillonia 15:20 09 Jul 2004

In design view, click in the 'data type' column. Choose date and time. This brings up option inbottom left corner. You can select a ready made format if you click in the fromat row from the drop down arrow or enter your own format.

Please ignore if I have stated the obvious.

jhm 20:04 09 Jul 2004

Had a quick look, but don't think it works that way. You can build an expression using Current Date minus DOB and specify answer in months ie 30. If divide by 12 the result is 2.5 yrs whereas the answer format wanted is 2.6 ( 2years and 6 months)

AccessMoron 13:51 13 Jul 2004

Change the function to return a string,
calculate the number of months, take the integer of that/12 as the years as the first part of the string, then take the remainder as months part of the string. So you should end up with 5 Years 6 months

jhm 14:19 13 Jul 2004

Sounds right enough, but where is all this done-in the expression builder??

AccessMoron 14:31 13 Jul 2004

Ceate a module and post this code into it

Public Function GetAge(dteDoB As Date, Optional dteTo As Date) As String
Dim intMonths As Integer
Dim intYears As Integer
Dim strOut As String
'If user does not supply a
'date to calculate to
'then take today as default
If dteTo = 0 Then
dteTo = Date
End If
'Get the number of months
intMonths = DateDiff("m", dteDoB, dteTo)
'calculate the number of years
intYears = Int(intMonths / 12)
'calculate the remaining
intMonths = intMonths - (intYears * 12)
'Format the years
Select Case intYears
Case 0
Case 1
strOut = intYears & " Year "
Case Else
strOut = intYears & " Years "
End Select
'Format the months
Select Case intMonths
Case 0
Case 1
strOut = strOut & intMonths & " Month"
Case Else
strOut = strOut & intMonths & " Months"
End Select

GetAge = strOut
End Function

Save the module.

in the exprssion builder of the query builder, if you now goto functions in the middle of the list, you will see Built in, then Your database. Select the module that you have just created.

then select get age.

in the expression window you shold noe have
GetAge(<<dteDoB>>, <<dteTo>>)
Replace <<dteDoB>> with the field name of the childs birthday

To get the current age delete , <<dteTo>>
to geat an age at a pecific time replace
<<dteTo>> with the date you want.

AccessMoron 14:33 13 Jul 2004

Sorry formating

should be

Public Function GetAge(dteDoB As Date, Optional dteTo As Date) As String

Dim intMonths As Integer

Dim intYears As Integer

Dim strOut As String

'If user does not supply a

'date to calculate to

'then take today as default

If dteTo = 0 Then

dteTo = Date

End If

'Get the number of months

intMonths = DateDiff("m", dteDoB, dteTo)

'calculate the number of years

intYears = Int(intMonths / 12)

'calculate the remaining

intMonths = intMonths - (intYears * 12)

'Format the years

Select Case intYears

Case 0

Case 1

strOut = intYears & " Year "

Case Else

strOut = intYears & " Years "

End Select

'Format the months

Select Case intMonths

Case 0

Case 1

strOut = strOut & intMonths & " Month"

Case Else

strOut = strOut & intMonths & " Months"

End Select

GetAge = strOut

End Function

jhm 15:55 13 Jul 2004

Gulp- it will take a little time to work through this. Wil let you know

jhm 21:12 13 Jul 2004

Entered as instructed but compiler comes up with a syntax error on last line ie
End Select GetAge = strOut

As this is the first time I have used Virtual Basic I have no idea what is wrong

AccessMoron 10:40 14 Jul 2004

Formatting again
End Select [PUT A RETURN HERE]GetAge = strOut

This thread is now locked and can not be replied to.

Elsewhere on IDG sites