Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    11

    Unanswered: Calculate Age as of August 1

    I have a membership database where I need to calculate the age of a person as of August 1 of each year. The age of the person would stay the same until the following August 1 where it would adjust age to next year.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This should do it for you, where DOB_Field is the actual name of the field you have the date of birth in:
    Code:
    Private Sub DOB_Field_AfterUpdate()
    If Not IsNull(Me.DOB_Field) Then
      Me.AgeAsOfAugust = DateDiff("yyyy", [DOB_Field], DateSerial(Year(Date), 8, 1)) - IIf(Format$(DateSerial(Year(Date), 8, 1), "mmdd") < Format$([DOB_Field], "mmdd"), 1, 0)
    End If
    End Sub
    
    Private Sub Form_Current()
    If Not IsNull(Me.DOB_Field) Then
      Me.AgeAsOfAugust = DateDiff("yyyy", [DOB_Field], DateSerial(Year(Date), 8, 1)) - IIf(Format$(DateSerial(Year(Date), 8, 1), "mmdd") < Format$([DOB_Field], "mmdd"), 1, 0)
    End If
    End Sub
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    This should do it for you, where DOB_Field is the actual name of the field you have the date of birth in and AgeAsOfAugust is the name of your unbound textbox:
    Code:
    Private Sub DOB_Field_AfterUpdate()
    If Not IsNull(Me.DOB_Field) Then
      Me.AgeAsOfAugust = DateDiff("yyyy", [DOB_Field], DateSerial(Year(Date), 8, 1)) - IIf(Format$(DateSerial(Year(Date), 8, 1), "mmdd") < Format$([DOB_Field], "mmdd"), 1, 0)
    End If
    End Sub
    
    Private Sub Form_Current()
    If Not IsNull(Me.DOB_Field) Then
      Me.AgeAsOfAugust = DateDiff("yyyy", [DOB_Field], DateSerial(Year(Date), 8, 1)) - IIf(Format$(DateSerial(Year(Date), 8, 1), "mmdd") < Format$([DOB_Field], "mmdd"), 1, 0)
    End If
    End Sub
    Linq ;0)>

    Sorry! Don't know why an edit double-posted!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •