Hi Douglas,
Thanks for the help.
I don't get an error now, but I still don't get the output Allen described.
I only get the ages of the persons divided by 5.
The following is in the Module:
============================
Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtDOB As Date
Dim dtAsOf As Date
Dim dtBDay As Date 'Birthday in the year of calculation.
Age = Null 'Initialize to Null
'Validate parameters
If IsDate(varDOB) Then
dtDOB = varDOB
If Not IsDate(varAsOf) Then 'Date to calculate age from.
dtAsOf = Date
Else
dtAsOf = varAsOf
End If
If dtAsOf >= dtDOB Then 'Calculate only if it's after person was
born.
dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
End If
End If
End Function
====================================
My Query is:
--------------------------------------------------------------------------
Field backname tussenvoeg firstname gebdatum
AgeBracket:Age([gebdatum])\5
--------------------------------------------------------------------------
Table members members members members
--------------------------------------------------------------------------
gebdatum=date of birth
What to do now?
Han
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht
You need the Age function from the link Allen gave you in his first response
http://members.iinet.net.au/~allenbrowne/func-08.html
Copy the code for that function into a new module and save the module. Do
not name the module Age: the module must be named something other than what
the function is named.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Thanks Allen,
I now get the errormessage :
The expression has a undefind function Age
My query is as follows:
--------------------------------------------------------------------------
--
------------------
Field backname tussenvoeg firstname gebdatum AgeBracket:
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--
------------------
"Allen Browne" <
[email protected]> schreef in bericht
Open your query in design view.
Type this into a fresh column of the grid, in the Field row:
AgeBracket: Age([BirthDate]) \ 5
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Hi Allen,
Thanks a lot for reaction, unfortunately I'm rather new with Access
and
don't understand how or where to put the code, in a table,
query,
form
or
report.
Maybe you can help with that.
Han
"Allen Browne" <
[email protected]> schreef in bericht
See the Age() function here:
http://members.iinet.net.au/~allenbrowne/func-08.html
The use integer division on the result, e.g.:
AgeBracket: Age([BirthDate]) \ 5
The result will be:
1 = 5 - 9;
2 = 10 - 14;
3 = 15 - 19;
...
You can then group by the result, to get the count of each group:
1. Depress the Totals button in query design (Sigma icon on toolbar).
Access adds a Total row to the grid.
2. In the Total row under AgeBracket, accept Group By under the
AgeBracket
field.
3. Add the primary key to the grid. In the Total row, choose Count.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
In a table called Members are the birthdays from the member
and
his
wife.
I want a report in which is counted how many members and
wifes
are
in
a
certain age group. The groups are all the time with 5 years
difference.
So
15 to 20, 21 to 25, 26 to 30 and so on.
Can you help me?
Thanks!
