how do i calculate an age with DOB? (1 Viewer)

[techexpressinc]

Re: here is the routine i installed

below is the routine


Option Compare Database
- Calculating a person's age in years, months and days

To accurately calculate a person's age in years, months and days, and (optionally) return that age in words, use the following syntax:
AgeYMD(DateSerial(1956,11,5), Date(), True, True, True)
Public Function AgeYMD(DOB As Date, today As Date, Optional WithMonths As Boolean = False, _
Optional WithDays As Boolean = False, Optional DisplayWithWords As Boolean = False) As Variant
'Author: © Copyright 2001 Pacific Database Pty Limited
' Graham R Seach gseach@pacificdb.com.au
' Phone: +61 2 9872 9594 Fax: +61 2 9872 9593
'
' You may freely use and distribute this code
' with any applications you may develop, on the
' condition that the copyright notice remains
' unchanged, and intact as part of the code. You
' may not sell or publish this code in any form
' without the express written permission of the
' copyright holder.
'
'Description: This function calculates a person's age,
' given their date of birth, and a second date.
'
'Inputs: DOB: The person's date of birth
' Today: The second date (ostensibly today)
' WithMonths: Boolean - If True, displays months
' DisplayWithWords: Boolean - If True, displays
' (ie: years / months)
'
'Outputs: On error: Null
' On no error: Variant containing person's age in
' years, months and days (if selected).
' If DisplayWithWords = False:
' Months and days, if selected, are shown
' to the right of the decimal point, but
' are the actual number of months and days,
' not a fraction of the year. For example,
' 44.11.03 = 44 years 11 months and 3 days.
' If DisplayWithWords = True:
' Output example: "44 years 11 months 3 days",
' except where months = 0, in which case, no
' months are shown.

On Error GoTo AgeYMD_ErrorHandler

Dim iYears As Integer
Dim iMonths As Integer
Dim iDays As Integer
Dim dTempDate As Date

'Check that the dates are valid
If Not (IsDate(DOB)) Or Not (IsDate(today)) Then
DoCmd.Beep
MsgBox "Invalid date.", vbOKOnly + vbInformation, "Invalid date"
GoTo AgeYMD_ErrorHandler
End If

'Check that DOB < Today
If DOB > today Then
DoCmd.Beep
MsgBox "Today must be greater than DOB.", _
vbOKOnly + vbInformation, "Invalid date position"
GoTo AgeYMD_ErrorHandler
End If

iYears = DateDiff("yyyy", DOB, today) - _
IIf(DateAdd("yyyy", DateDiff("yyyy", DOB, today), DOB) > today, 1, 0)
dTempDate = DateAdd("yyyy", iYears, DOB)

If WithMonths Then
iMonths = DateDiff("m", dTempDate, today) - _
IIf(DateAdd("m", iMonths, DateAdd("yyyy", iYears, DOB)) > today, 1, 0)
dTempDate = DateAdd("m", iMonths, dTempDate)
End If

If WithDays Then
iDays = today - dTempDate
End If

'Format the output
If DisplayWithWords Then
'Display the output in words
AgeYMD = IIf(iYears > 0, iYears & " year" & IIf(iYears <> 1, "s ", " "), "")
AgeYMD = AgeYMD & IIf(WithMonths, iMonths & " month" & IIf(iMonths <> 1, "s ", " "), "")
AgeYMD = Trim(AgeYMD & IIf(WithDays, iDays & " day" & IIf(iDays <> 1, "s", ""), ""))
Else
'Display the output in the format yy.mm.dd
AgeYMD = Trim(iYears & IIf(WithMonths, "." & Format(iMonths, "00"), "") _
& IIf(WithDays, "." & Format(iDays, "00"), ""))
End If

Exit_AgeYMD:
Exit Function

AgeYMD_ErrorHandler:
AgeYMD = Null
Resume Exit_AgeYMD
End Function__________________

 

[namliam]

It says how to use it in the title

AgeYMD(DateSerial(1956,11,5), Date(), True, True, True)

You can leave out the True,True,True part... if you only want years
AgeYMD(DateSerial(1956,11,5), Date())

offcourse you replace the DateSerial() thing by your DoB field

 

[techexpressinc]

So I put this "AgeYMD(DateSerial(1956,11,5), Date())" into the file box after clicking the "Expression Builder" and replace "DateSerial(1956,11,5)" with my field name, that is table.field, which is for me "Members.DOB". This was not all so obvious to me. Will I did and got the message "Compile error". I think I am not getting the syntax correct on the field Members.DOB.
"Expr1: AgeYMD([MembersOB],Date())" Can I get a pointer here now? Thank you for sticking with me your repetition went up.

 

[neileg]

Not sure why you need a user defined function. DateDiff() will return age in years.

 

[techexpressinc]

So, neileg - you are saying I just open the expression builder and type
"DateDiff(Members.DOB Date())"
without the quotes and it the query will display the years old of a person?

 

[namliam]

datediff("YYYY",#31-12-2008#,Date())
Will return 1 though, eventhough you dont actually reach the age of 1 till dec 31 2009.

This function does WAY more than what you actually need... but it works....
Copy above functon into a module, save it.
In your query type:
Years old: ageYMD(Yourdatefield,Date())

That should work

 

[techexpressinc]

namliam - I did as you said "AgeYMD(Yourdatefield,Date())" without the quotes and still got a error "Compie error. in query expression 'AgeYMD([members].[DOB],Date()".

Attached is a screen image of what I have.

Please direct thank you.

 

[Brianwarnock]

Remove these lines from your function, which actually starts at Public Function etc, it might have been simpler for a newbie to start with my simple function, but never mind.

Brian

Option Compare Database
- Calculating a person's age in years, months and days

To accurately calculate a person's age in years, months and days, and (optionally) return that age in words, use the following syntax:
AgeYMD(DateSerial(1956,11,5), Date(), True, True, True)

 

[JANR]

I could be the comma, try AgeYMD([members].[DOB];Date()) or wrap ([members].[DOB])

JR

 

[raskew]

This has got to be a new record. 30 posts and still no solution.

From the jpg, it would appear that the module may have the same name as the function -- that's a no-no and could result in the error you're encountered.

Suggestion:

1) Open a new module.
2) Copy/paste Brian's AgeInYears() function (Post #10) to the module.
3) Close/save the module. It'll probably offer Module1 as the suggested module name. Go with that.
4) Go to the immediate window and test, e.g.:

? ageinyears(#4/1/53#, date())
55

Bob

 

[techexpressinc]

Re: Still trying to calculate an age with DOB?

I did this one "AgeYMD([Members].[DOB];Date())"
without the quotes and still received a syntax error
the message on the Run was =
"The expression you entered contains an invalid syntax.:

and then tried this one "AgeYMD(([Members].[DOB]);Date())"
without the quotes and still received the same message syntax error.

Attached is the lastest 10:30 est snapshot of the field as it is coded.

Please keep directing me. Thank you.

 

[Brianwarnock]

Hi Bob, I did say just about all of that many posts back, I think it may be a case of too many cooks spoiling the broth. It wasn't helped by some posters answering the wrong question to start with.

Brian

 

[techexpressinc]

Re: Trying a new module now

Bob - ok I am trying the code from post 10 - i think i installed it in the db.

I was not sure how to "go to the immediate window and test, e.g.:"

I think you mean to start a new query with just
"ageinyears(#4/1/53#, date())" 55

i tried that and failed please see the attachment.

Thank you for it seems like it is a lot harder than it should be for a person who has done a lot of Excel and a little of Access.

Thanks for keep following this process.
Russ

 

[Brianwarnock]

The first thing I noticed was that you have 3 modules , 2 are the same name as your functions despite it being stated more than once that that is not allowed. Note that a Module can contain many functions, I realise that you were misled early in the thread , but copy the functions into module1 and delete the other modules, then try again.

Brian

 

[Brianwarnock]

See attached for a simple eample as to how it all hangs together.

Brian

 

[techexpressinc]

Great - Thanks for the example. I did a quick run of it and saw it worked!
I will try to incorporated it later today. I have to do some productive work for a little bit. I spend 3 hours plus so far on the silly age thing. I could have manually figured it out for the 101 persons, twice by now. But, if will be better for the future if the age is calculated within MS-Access.
Russ

 

[techexpressinc]

If you do install the VB code remember to drop the comments in the front I did not and caused my Switchboard to crash. The words before "Public Function":


"- Calculating a person's age in years, months and days


To accurately calculate a person's age in years, months and days, and (optionally) return that age in words, use the following syntax:

AgeYMD(DateSerial(1956,11,5), Date(), True, True, True)- Calculating a person's age in years, months and days
"