boldDOB age ranges calculations 1

[Daina]

Hi,

I am having trouble finding an appropriate code for my form, I will try to explain

I have a table "tblPersonalDetails" which has amongst other fields a DOB field.

On another table "tblWeeklyImmunisationProgram" I have among other fields 4 separate fields 0-6 yrs, 7-12 yrs, 13-18 yrs and Adults

What I need to do is show a total for each age group in the "tblWeeklyImmunisationProgram" under each respective age group which would constantly update.

can anyone help me please?

Daina

 

[SBendBuckeye]

I found this age calculation in FAQ faq181-85.

Age = DateDiff("yyyy", Birthdate, Enddate) + _
(Enddate < DateSerial(Year(Enddate), Month(Birthdate), _
Day(Birthdate)))

The code above determines age by subtracting the years from the dates in question and then adds a slight twist at the end. If EndDate is less than the current year birthday the expression evaluates to false which is a -1. So if you haven't had your birthday yet this year, subtract 1 from the number of years calculated by DateDiff.

To pull all that together for your form, try something like the following:

1. Define 5 text boxes, txt0to6, txt7to12, txt13to18,
txtAdult and txtEndDate (make invisible if desired).

2. I could only get this to work with Access predefined
functions so if you need an End Date other than Now(),
etc. you can set txtEndDate in your Form Load event code,
otherwise let it default to Now().

3. Use the DCount function to return counts for each of them

In general, it will look like this:

txt0to6 = DCount(&quot;DOBFieldName&quot;, &quot;tblPersonalDetails&quot;, _
&quot;Age() < &quot; & 7)

However, you will need to substitute the whole expression
above for Age() including txtEndDate so it will look like
the code below, assuming Birthday is your fieldname:

txt0to6 = DCount(&quot;Birthday&quot;, &quot;tblPersonalDetails&quot;, _
& &quot;DateDiff(&quot;&quot;yyyy&quot;&quot;, Birthday, txtEndDate) + &quot; _
& &quot;(txtEndDate < DateSerial(Year(txtEndDate), _
& &quot;Month(Birthday), Day(Birthday))) < &quot; & 7)

If your fieldname is different from Birthday, just
substitute your fieldname for Birthday above and below.

Similarly, the others are below:

txt7to12 = DCount(&quot;Birthday&quot;, &quot;tblPersonalDetails&quot;, _
& &quot;DateDiff(&quot;&quot;yyyy&quot;&quot;, Birthday, txtEndDate) + &quot; _
& &quot;(txtEndDate < DateSerial(Year(txtEndDate), _
& &quot;Month(Birthday), Day(Birthday))) < &quot; & 13) _
- txt0to6

txt13to18 = DCount(&quot;Birthday&quot;, &quot;tblPersonalDetails&quot;, _
& &quot;DateDiff(&quot;&quot;yyyy&quot;&quot;, Birthday, txtEndDate) + &quot; _
& &quot;(txtEndDate < DateSerial(Year(txtEndDate), _
& &quot;Month(Birthday), Day(Birthday))) < &quot; & 19) _
- txt0to6 - txt7to12

txtAdult = DCount(&quot;Birthday&quot;, &quot;tblPersonalDetails&quot;, _
& &quot;DateDiff(&quot;&quot;yyyy&quot;&quot;, Birthday, txtEndDate) + &quot; _
& &quot;(txtEndDate < DateSerial(Year(txtEndDate), _
& &quot;Month(Birthday), Day(Birthday))) > &quot; & 18)

For txt7to12 and txt13to18 I did one Count and then
subtracted the younger counts saved in the other text
boxes.

4. Put the above in your Form Current processing if you will
be making changes to tblPersonalDetails or in the Form
Load or Form Open code if it will be static data. If you
put it in the Form Current code it will be recalculated
each time. I'm giving extra detail here because I don't
know what level of expertise you have in VBA.

Good Luck! Thanks for an interesting question! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[MichaelRed]

The table

[b]ContID	LastName	FirstName	DOB[/b]
1	        Doe	        John	         11/25/90
2	        Smith	        Roberg	         10/23/97
3	        Mary	        O'Conner	 8/21/85
4	        Karen	        Blessing	 12:00:00 AM
5	        Jeane	        Knight	         7/22/88
6	        Budd	        Billy	         9/30/80

the (select) query - which categorizes the age into a group

SELECT tblDobTest.DOB, DateDiff(&quot;yyyy&quot;,[DOB],[EndDate]) AS Age, IIf([Age]>0 And [Age]<16,&quot;1-15&quot;,IIf([Age]>15 And [Age]<21,&quot;16-20&quot;,IIf([Age]>20 And [Age]<26,&quot;21-25&quot;,IIf([Age]>25 And [Age]<31,&quot;25-30&quot;,IIf([Age]>30 And [Age]<36,&quot;30-35&quot;,&quot;UnKnown&quot;))))) AS AgeGrp, IIf([Age]>0 And [Age]<6,1,0) AS [Age0-6], IIf([Age]>6 And [Age]<12,1,0) AS [Age7-12], IIf([Age]>12 And [Age]<18,1,0) AS [Age12-18], IIf([Age]>18,1,0) AS AgeAdult
FROM tblDobTest;

The proocedure to calculate the individual's age

Public Function basDOB2Age(DOB As Date, Optional AsOf As Date = -1) As Integer

    'Dob is just the date of Birth
    'AsOf is an optional date to check - as in examples 1 & 2
    'Otherwise, the DOB is checked against the Current Date

    'Michael Red 12/15/2001
    'To Calculate Age from Date of Birth
    
    'Sample Useage:
    '? basDOB2Age(#8/21/1942#, #8/21/2022#)
    '80

    '? basDOB2Age(#8/21/1942#, #8/20/2022#)
    '79

    '? basDOB2Age(#8/21/1942#)
    '59


    Dim tmpAge As Integer       'Simple DateDiff w/o Birthday correction
    Dim BrthDayCorr As Boolean  'BirthDay Before or After date in question

    If (AsOf = -1) Then         'Check for (Optional Date to Check against)
        AsOf = Date             'If Not Supplied, Assume Today
    End If

    tmpAge = DateDiff("YYYY", DOB, AsOf)        'Just the Years considering Jan 1, Mam
    BrthDayCorr = DateSerial(Year(AsOf), Month(DOB), Day(DOB)) > AsOf   'Check This Year

    basDOB2Age = tmpAge + BrthDayCorr           'Just Years and Correction
    
End Function

The aggregate query counte (actually SUMS) the number of individuals in each age group

SELECT Sum(qryDobTest.[Age0-6]) AS [Age0-6], Sum(qryDobTest.[Age7-12]) AS [Age7-12], Sum(qryDobTest.[Age12-18]) AS [Age12-18], Sum(qryDobTest.AgeAdult) AS AgeAdult
FROM qryDobTest
WITH OWNERACCESS OPTION;

The aggregate query results

Age0-6	Age7-12	Age12-18   AgeAdult
1	0	2	   2

The above -with appropiate substutions of field names in hte source table provides all of the information in a single (recordset) source. Just use the recordset source as hte assigned value to the text boxes on the form.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Daina]

MichaelRed,

Sorry but I did not understand your reply at all, a little to advanced for me but thank you anyway.

SBendBuckeye,

Thank you for your reply, as to my expertise in VBA I should say not a lot really I love creating databases and working in Access, but when it comes to writing expressions I have had no training and can not find anywhere to attend a course on expression writing so I am left to posting questions to this forum and pray that someone can help me out with the correct expression.

As for your reply I added this to the control source of an unbound text box on my form

= DCount(&quot;Date of Birth&quot;, &quot;tblPersonalDetails&quot;, _ & &quot;DateDiff(&quot;&quot;yyyy&quot;&quot;, Date of Birth, txtEndDate) + &quot; _ & &quot;(txtEndDate < DateSerial(Year(txtEndDate), _ & &quot;Month(Date of Birth), Day(Date of Birth))) < &quot; & 7)

I substituted the &quot;Birthday&quot; for the name of my table &quot;Date of Birth&quot; but otherwise left it as you had written it and this is what came up From Access:

The expression you entered contains invalid syntax.

You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.

I studied the expression but could not fix it do you know what is wrong?

Also, do I add this code (when correct) into the control source of the text box and all of the codes for each age group to the on current section of the form?

In my &quot;tblPersonalDetails&quot; I have the &quot;Date of Birth&quot; field and an unbound txt box with
=Format(Now()-[Date of Birth],&quot;yy&quot
written in the control source which displays the age in a diffferent box.

Does this make any difference. I hope I have'nt confused you too much, looking forward to your reply

Cheers

Dee

 

[SBendBuckeye]

The DCount function takes 3 parms as follows for a numeric field: DCount = (&quot;FldName&quot;, &quot;TblName&quot;, &quot;Criteria = &quot; & val)

The underscores _ and ampersands & above were just to make it easier to see by breaking it up into several lines. Since this is a calculated value, I would put it in the form On Current event code as follows, assuming that the unbound control on your fome is named ctlName. Also, since you have embedded spaces in your field names, you must put square brackets around them:

ctlName = DCount(&quot;[Date of Birth]&quot;, &quot;tblPersonalDetails&quot;, _
& &quot;DateDiff(&quot;&quot;yyyy&quot;&quot;, [Date of Birth], txtEndDate) + &quot; _
& &quot;(txtEndDate < DateSerial(Year(txtEndDate), _
& &quot;Month([Date of Birth]), Day([Date of Birth]))) < &quot; & 7)

Substitute your actual form unbound control name for ctlName above in the FormCurrent code and see what happens. I will try to check the board again this afternoon in case you have more questions.

Good Luck!

PS. As pertains to MichaelRed, he is excellent but can be complex. Just to get you started here is what he is doing with a part of the above:

SELECT tblDobTest.DOB,
DateDiff(&quot;yyyy&quot;,[DOB],[EndDate]) AS Age,
IIf([Age]>0 And [Age]<16,&quot;1-15&quot;,
IIf([Age]>15 And [Age]<21, &quot;16-20&quot;,
IIf([Age]>20 And [Age]<26,&quot;21-25&quot;,
IIf([Age]>25 And [Age]<31,&quot;25-30&quot;,
IIf([Age]>30 And [Age]<36,&quot;30-35&quot;,&quot;UnKnown&quot)))) AS AgeGrp
FROM tblDobTest

He uses the DateDiff Function to return the year difference between DOB and Enddata as a calculated column called Age.
He then uses Age in a nested IIF statement to return a calculated text column name AgeGrp (this would contain the string &quot;1-15&quot;, &quot;25-30&quot;, etc. He then uses that later in his code to do other things.

Keep persisting - its always fun to learn and/or teach others who want to learn!

Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!