calculating age

[MarionD]

Hi again!

I really would appreciate some help on this:

I have a form where I enter a date:- Then I have an option group called day/week/month/quarter/year

now I need to filter the subform to display all children
who turn 2 on this date if day is chosen
who turn 2 in this week if week is chosen
who turn 2 in this month etc...

Somethin like "select * from tbl_Children where the datediff between entered date and birthdate =2

Help!
Marion

 

[jdraw]

Here is a routine to calculate Age

'---------------------------------------------------------------------------------------
' Procedure : Age
' Author    : Jack (from awf)
' Date      : 06-09-2012
' Purpose   : This routine determines the Age of a Person given their DOB.
' It accounts for the birthday this year (whether passed or not). A second parameter
' Specdate allows you to work from a different Date than today's date.
'If SpecDate is missing, the routine defaults to today's date.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Public Function Age(dteDOB As Date, Optional SpecDate As Variant) As Integer
      Dim dteBase As Date, intCurrent As Date, intEstAge As Integer
10       On Error GoTo Age_Error

20    If IsMissing(SpecDate) Then
30    dteBase = Date
40    Else
50    dteBase = SpecDate
60    End If
70    intEstAge = DateDiff("yyyy", dteDOB, dteBase)
80    intCurrent = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
90    Age = intEstAge + (dteBase < intCurrent)

100      On Error GoTo 0
110      Exit Function

Age_Error:

120       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Age of Module AWF_Related"
End Function

and a routine to test the Age function

'---------------------------------------------------------------------------------------
' Procedure : TestAge
' Author    : Jack
' Date      : 06-09-2012
' Purpose   : Routine to show the current age of a person given their DOB.
'This accounts for whether their birthday this year has occurred  or not.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub TestAge()

Dim jage As Date
   On Error GoTo TestAge_Error

jage = #9/6/1919#
Debug.Print Age(jage)

   On Error GoTo 0
   Exit Sub

TestAge_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure TestAge of Module AWF_Related"

End Sub

I hope these are useful to you.

 

[MarionD]

wow thats wonderful! Thank you so much!
Marion

 

[jdraw]

You're welcome. Good luck with your project.