Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    12

    Unanswered: Trouble with date calculation

    I seem to be having difficulty calculating dates. I have a "date of birth" column with a "date/time" data type which displays a "medium date" (i.e. 19-Jun-07).

    I have registration form for some programs and what I would like to do is calculate the age of the registrant for 1 Sep 11. If they are over 18 at this date, then they must participate in the adult program. What is the syntax to return the age for a projected date? The now() function does not seem to allow for those who have birthdays between todays date and the projected date.

    Thanks

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Don't know what you want to do if the age on 1-Sep-2011 is over 18, but the following code will return the age on this date, into the unbound control named Age_1_9_2011, based on a 'date of birth' Control named DOB, and can easily be modified to do whatever if the returned age is over 18.

    Code:
    Private Sub DOB_AfterUpdate()
     If Nz(Me.DOB, "") <> "" Then
      Me.Age_1_9_2011 = DateDiff("yyyy", [DOB], #1/9/2011#) - IIf(Format$(#1/9/2011#, "mmdd") < Format$([DOB], "mmdd"), 1, 0)
     End If
    End Sub
    Code:
    Private Sub Form_Current()
    If Nz(Me.DOB, "") <> "" Then
      Me.Age_1_9_2011 = DateDiff("yyyy", [DOB], #1/9/2011#) - IIf(Format$(#1/9/2011#, "mmdd") < Format$([DOB], "mmdd"), 1, 0)
     End If
    End Sub
    BTW, Now() returns Date and Time, while Date() returns the Date only. Now() should only be used if the Time component is absolutely necessary.

    If you wanted an unbound Control to reflect the current age anytime you accessed a record, in the code above you would simply replace

    #1/9/2011#

    with

    Date.

    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
    Apr 2011
    Posts
    12

    Solution worked just fine

    Thanks - it did the trick. I also found another post where the user had similar problems and he utilized this solution which I incorporated
    Code:
    DateDiff("yyyy", [Date_of_Birth], DateSerial(Year(Date), 9, 1))
    Thanks for your help

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19

    Arrow

    Not sure what you mean by 'incorporate,' but if you mean you're actually using this function, as you just gave it, for your application, you'll find it will not always accurately return the age on September 1! It will only give an accurate age for people whose birthdays occur on or after 1 September.

    DateDiff() using "yyyy" as the interval only calculates the difference in years! In other words,

    DateDiff("yyyy",#12/31/2011#, #1/1/2012#)

    will return 1 year as the difference between 12/31/2011 and 1/1/2012, even though the two dates are only 1 day apart, because it simply uses the year component and calculates the difference between 2012 and 2011.

    Now you can replace

    #1/9/2011#


    in the code I gave you with

    DateSerial(Year(Date), 9, 1)

    to make it calculate the age on September 1 of whatever the current year is, so you don't have to re-code it every year. If that's what you meant by 'incorporate' that's fine.

    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

Posting Permissions

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