Results 1 to 7 of 7

Thread: Age (access 97)

  1. #1
    New Lounger
    Join Date
    Jan 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Age (access 97)

    Hello everyone,
    I've got a simple one (?) I've got two fields, Birthdate (Their birthdate) and StDate (The date they enter our school). I want to calculate their age in years at the time they enroll (StDate) as another field (EnrollAge). DateDiff was no good (simply subtracted the years) and Age() and AgeMonths() calculate using Now, which keeps giving their current age, instead of the locked in age that they were when they signed up. I'm hoping for a function, as my programming skill are rudimentary at best.

    Any help is appreciated,
    Samuelww

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Age (access 97)

    Stick this function in a module, and see if it works. It probably could be compressed into a singe IIF statement, but it is easier to read the way I wrote it.
    <pre>Public Function GetAgeAt(BirthDate as date, OtherDate as date) As Integer
    Dim intAge As Integer

    intAge = DateDiff("yyyy", BirthDate, OtherDate)
    If DateAdd("yyyy", intAge, BirthDate) > OtherDate Then
    GetAgeAt = intAge - 1
    Else
    GetAgeAt = intAge
    End If
    End Function
    </pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age (access 97)

    Mark,
    Thanks for your quick reply. I'll try it and let you know.
    Thanks,
    Sam

  4. #4
    Lounger
    Join Date
    Sep 2001
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age (access 97)

    I just recently did it calculating the months and dividing by 12. Then setting the number to Fixed and the decimal to 1. I like seeing if a child is 14.1 or 14.9. Then I can easily tell their approximate birthday.

    =(DateDiff("m",[Date_of_Birth],Now()))/12

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Age (access 97)

    >> =(DateDiff("m",[Date_of_Birth],Now()))/12 <<

    There is a flaw in this equation. The nature of how DateDiff works is that it counts the number of different months between 2 dates, and not the number of 30-day intervals, etc. For example, take this calcuation of the difference between 2 consecutive dates:
    '
    ' first look at datediff for 1-day interval 12/30/01 to 12/31/01
    DateDiff("m",#12/30/01#, #12/31/01#) returns 0
    DateDiff("yyyy",#12/30/01#, #12/31/01#) returns 0
    '
    ' now look at another 1-day interval: 12/31/01 to 1/1/02
    DateDiff("m",#12/31/01#, #1/1/02#) returns 1
    DateDiff("yyyy",#12/31/01#, #1/1/02#) returns 1
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    New Lounger
    Join Date
    Jan 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age (access 97)

    To Mark and Sandy,
    Well, actually I thought of something that is similar to what Sandy suggested. Instead of using months I used days and divided by 365.25. Seems to work pretty good!
    To Mark-most of the suggestions in this group seem to be in code, which I have so far successfully avoided learning. However, I'm getting the distinct impression I may need to learn at least a little if I want to do much more customization. Two questions: is there a good source for third party add-ons for Access 97 and is there a good beginner's book for writing code for Access, like "Access VBA for Dummies"?

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Age (access 97)

    In all honesty, if you want to develop in Access you will have to learn VBA. The book I recommend (and the one I have) is "Beginning Access97 VBA" by Sussman and Smith; althought I'm sure the have updated versions.

    To use the function I wrote, you merely have to paste it into a module. You can then call it from anywhere. In a query, the expression would be something like this:

    EnrollmentAge: GetAgeAt(birthdate, enrollmentDate)

    In a report or a form, you can set the control source of a form to be:
    =GetAgeAt(birthdate, enrollmentDate)

    You can get the current age by using: GetAgeAt(Birthdate)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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