I have a DOB on the database.
Table = Members
Field = DOB
I seen this as a formula - “AgeCalcField: AgeInYears([DOBField], Date())” -

Within my query I do this
I click the field box
and coded an expression as listed here and get a syntax error:
“AgeCalcField: AgeInYears([Members:DOB], Date())”

Can someone help in getting the age on my query working?
Thanks a lot - Russ @ scaninc.org

I can only suspect the : in the field name. members:dob

Brian

AgeInYears is not a default access function but rather a self build function.
This function you need to find if you want to use it.

I suggest you search this forum for Date of Birth and find it....

You are right the field with the simley is "members:dob" why it displayed has a simley is beyond me as usual.
Thx Rus

Will a missing function give a syntax error?

Brian

Do you have the module: 'AgeInYears' stored in your database?

You are right the field with the simley is "members:dob" why it displayed has a simley is beyond me as usual.
Thx Rus

You have to select Disable smilies in text in the options on the post submittal, is that a word?, page. : D without a space is :D


Brian

I do not have the module: 'AgeInYears' stored in your database? - if needed how would i add it ?

Heres one that you can use:

- 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

Its not a module but a function that is missing, the function is stored in a module which must have a different name, use the default Module1 if this is the only function you have.

A simple function is below, its error checking merely exits , you may want to do more.

Brian


Public Function Ageinyears(dtDOB As Variant, dtDOC As Variant) As Integer

Dim dtBDay As Date
If Not IsDate(dtDOB) Or Not IsDate(dtDOC) Then Exit Function
dtBDay = DateSerial(Year(dtDOC), Month(dtDOB), Day(dtDOB))
ageinyears = DateDiff("yyyy", dtDOB, dtDOC) + (dtBDay > dtDOC)

End Function

I meant to add that as far as I thought a missing function would give an Undefined Function error, not a syntax error, maybe you have both.

Brian

Ok i am getting closer... How do I store the above function in my DB and use it?

Sorry for any confusion I caused, don't know why I typed 'module' instead of 'function'

From DB objects select modules, you will see the default Module1 on the right select it in design view and copy and paste the code into it.

You use the function as you were attempting to in the earlier post.

Brian

Slowly getting there I am trying this in my expression and getting a syntax error now;
"AgeYMD [(Members.DOB) (Stays.Start Date)]"
I added the function i think:
Under the objects - Modules - I have AgeYMD.
Can I get some more help - thanks. Russ

AgeYMD [(Members.DOB) (Stays.Start Date)] Should be
AgeYMD([Members].[DOB], [Stays].[Start Date])

Brian

AgeInYears is not a default access function but rather a self build function.
This function you need to find if you want to use it.

I suggest you search this forum for Date of Birth and find it....

Once again I seem to be invisible?

I installed one "function" for this yesterday. This is i.e. like VB. Can you gave me instructions on how to use it.

Attached a screen print of the "function".

Brian showed you (more or less) how, also your screeny is missing.

My screen image thingy was too big. So, I reattached with it. I have a module added I think to do the code. I just do not know how to use it.

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__________________

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

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([Members:DOB],Date())" Can I get a pointer here now? Thank you for sticking with me your repetition went up.

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

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?

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

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.

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)

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

JR

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

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.

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

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

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

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

Brian

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

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
"