Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Join Date
    Mar 2004
    Location
    Eden ,NorthCarolina
    Posts
    7

    Unanswered: Help Me Please! Calculating Age

    I need help ASAP, I'm still learning this software so please be patience with me. I really don't know where to start as far as creating a calculation for this particular project, but I need this to complete the task.
    I have a database where I want to keep track of each individual
    age. I want the age to change every year.
    For example, I have one field I have birthdate, and in another I have the age. This is how I have this information in my data base 11/25/85 in one field for birth date and the age 18 in another field. I want the age to change every year. Is there some where in the database that I can put this calculation so I don't have to do this every time in the query. If not
    please show me how to create the calculation

    Thanks In Advance!

  2. #2
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32

    Re: Help Me Please! Calculating Age

    Originally posted by wrighj1
    I need help ASAP, I'm still learning this software so please be patience with me. I really don't know where to start as far as creating a calculation for this particular project, but I need this to complete the task.
    I have a database where I want to keep track of each individual
    age. I want the age to change every year.
    For example, I have one field I have birthdate, and in another I have the age. This is how I have this information in my data base 11/25/85 in one field for birth date and the age 18 in another field. I want the age to change every year. Is there some where in the database that I can put this calculation so I don't have to do this every time in the query. If not
    please show me how to create the calculation

    Thanks In Advance!
    Hi,

    Try searching this forum with keywords: Age DateDiff.

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Take a look at this routine I created quite a long time ago. You will, of course, have to change the field names to suit your needs:

    Code:
    Public Sub GetAge()
        If Not IsNull(Me.Birthdate) Then
                'Years Old
                Me.Age = Year(Now()) - Year(Me.Birthdate)
                If Month(Now()) < Month(Me.Birthdate) Then Me.Age = (Year(Now()) - Year(Me.Birthdate)) - 1
                Me.AgeTypeLabel.Caption = "year(s) old."
        
                'Months Old
                If Me.Age = 0 Then
                        Me.Age = DateDiff("m", Now, Me.Birthdate)
                        Me.AgeTypeLabel.Caption = "month(s) old."
                End If
    
                'Weeks Old
                If Me.Age = 0 Then
                        Me.Age = DateDiff("ww", Now, Me.Birthdate)
                        Me.AgeTypeLabel.Caption = "week(s) old."
                End If
        
                'Days Old
                If Me.Age = 0 Then
                        Me.Age = DateDiff("d", Now, Me.Birthdate)
                        Me.AgeTypeLabel.Caption = "day(s) old."
                End If
        End If
    End Sub
    Usage:

    Call GetAge

    You can place this into your Form's On Current event so that the age is automatically update when the record is selected or displayed. You could also modify this routine so that the table is automatically updated when the database is started.


  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can copy and past the next in a new Query, make the necessary changes concerning Table name and fields, it will calculate the age using SQL

    SELECT Geburtsdatum
    , year(date()) - year(Geburtsdatum)
    - iif(Month(Date()) > Month(Geburtsdatum),0,
    iif(Month(Date()) < Month(Geburtsdatum),1,
    iif(Day(Date()) < Day(Geburtsdatum),1,0))) as age
    FROM yourTable

    Happy weekend

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    My recommendation is not to have an age column because as you've noticed, you will have to do mass updates everyday to check and see if someone's birthday has occurred.

    Let SQL or a function calculate that when you run a report or query. Fields which must constantly be calculated and updated really shouldn't be stored in the database, but calculated at run-time.

    Hope that helps
    Last edited by ss659; 03-19-04 at 10:52.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    In truth, all you need is one line from one of the previous posters sub:

    =year(Now()) - year(Me.Birthdate)

    That's it.

  7. #7
    Join Date
    Mar 2004
    Posts
    52
    What is,
    me.birthdate?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by hali99
    What is,
    me.birthdate?
    In context it is the control name of a textbox holding the date to be evaluated.

  9. #9
    Join Date
    Mar 2004
    Posts
    52
    Thanx for the reply Ted.

    I am new to Access and am designing a database for a gym. I thought that I would have members D.O.B and Age in the Member Table. How would I change the given code so that the age changes every year? Would I replace Me.Birthdate with MemberAge?

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by hali99
    Thanx for the reply Ted.

    I am new to Access and am designing a database for a gym. I thought that I would have members D.O.B and Age in the Member Table. How would I change the given code so that the age changes every year? Would I replace Me.Birthdate with MemberAge?
    You really should not store age in the actual table, as you would have to write a procedure to update the ages every single day.

    If you have :

    Member ID ------ DOB ------------------- AGE
    1 -----------------01-MAR-1981-------------23
    2 -----------------01-APR-1981-------------22
    3 -----------------20-MAR-1981------------22

    Now member three is 22 today (March 19th). But tomorrow he's 23. So every single day, you would have to write a procedure to go through every single member and update the column.

    What you should do is what has been suggested before - when you have a form for entering information, or displaying it, set up a text box that will automatically calculate the member's age based on the DOB stored in the table. The code to generate the age has been given in several of the previous posts.

    Any calculations like that that need to be done every day should not be stored in the actual table. It is easier to write a quick function or query to do it at run-time

  11. #11
    Join Date
    Mar 2004
    Posts
    52
    Ok, So do I write this: =year(Now()) - year(MemberDOB) in the condition of the query? I have done this but I get an error message.

  12. #12
    Join Date
    Mar 2004
    Posts
    33
    A couple of questions to clarify before I post a response.

    I totally agree with all of the previous posts that indicate optimally this should be a run time calculation in the form's OnCurrent event.

    Are you planning a mailing to members based on how long they have been members? (Example: (Automated annual renewal reminder)

    Do you need to know how many days have passed since the member became a member? (Example: (Expiration of trial period)

    Do you need to know how many days are left until the members birthday? (Example: Birthday Reminder)

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by Teddy
    In truth, all you need is one line from one of the previous posters sub:

    =year(Now()) - year(Me.Birthdate)
    no, Teddy, sorry, that is not the right answer

    suppose birthdate is 1971-08-13 and today is 2004-03-30

    your simple subtraction of the years yields 33

    however, the person has not reached his birthday yet this year, so he's still only 32

    and i just wanted to say Vielen Dank to hammbakka, that's my code!! (see How do I calculate Age?)

    it is so nice when someone else picks up my code and uses it



    p.s. wrighj1, i totally agree with the others: do not store derived data
    Last edited by r937; 03-20-04 at 09:24.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    actually, Teddy's "cheat" is out by almost 2000 years.

    A's date-types are DaysdotFractionalDays since 1/1/100 00:00:00
    These are signed values.
    date = 0 falls on 30/12/1899 00:00:00

    subtract a pair A date-types and you get the difference in DaysdotFractionalDays .

    age calculated this way in days is correct

    age calculated from year(dt - now()) has a huge offset (gives my age today as close to two thousand years old (and sometimes i feel that old)) plus rudy's possible one-year error.

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Mar 2004
    Posts
    52
    Originally posted by toliver
    A couple of questions to clarify before I post a response.

    I totally agree with all of the previous posts that indicate optimally this should be a run time calculation in the form's OnCurrent event.

    Are you planning a mailing to members based on how long they have been members? (Example: (Automated annual renewal reminder)

    Do you need to know how many days have passed since the member became a member? (Example: (Expiration of trial period)

    Do you need to know how many days are left until the members birthday? (Example: Birthday Reminder)

    Oliver one of the things that I require is to send reminders to members that there gym membership will expire with 20 days. My uni lecturer told me to try: SYSDATE()-10 but that just gives me errors.

    Just to bump up my marks, I would like to know how I can check which members are under 35 or 25. This will be used for marketting purposes. Therefore I will need a way to calculate age or have the system provide me with a birthday reminder?

    I am not very advance with Access so I may not understand some terminology.

    Thanks for the help

Posting Permissions

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