Results 1 to 7 of 7

Thread: Recording Age

  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Recording Age

    This should be simple but I just cant get it to work! Can anyone please advise what I am doing wrong?

    I have a form called ClientDetailsForm(NEW) On this form I have the following fields:

    I have a text box Called CDateOfBirth (has a control source of the same name linked to a table called Client Details).

    I have another unbound text box called CAge in the control source I have the following formula =DateDiff("yyyy",[CDateOfBirth],Date()) This calculates the clients current age and works perfectly. (ie when I change the date of birth, this field automatically changes to reflect the new age.

    I have another Bound text box called CAgeAtTOIR (this field is designed to keep the client's age at Time of Initial Referral).

    What I want to do is, once the CAge is calculated, the CAgeAtTOIR is checked to see if the field already has an age entered. If the field is Null or "" then the CAgeAtTOIR is updated to hold the age which was calculated and is held in the CAge field. (If there is already a figure entered then there should be no update when the CAge field is auto calculated).

    I have been trying all sorts of different events in the CAge field (ie After Update - On Dirty -On change) to get this to work, to no avail.

    Can anyone suggest what code should be used and if I am coming about this in the wrong way?

    Thanks heaps for any suggestions

    KD

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    FYI - your formula is not working - Age is more complicated to calculate than that.
    I would record date of entry and work out age at time entry from that. You still only store one column's worth of data but you have more information since you can drive what you need from it..

  3. #3
    Join Date
    May 2002
    Posts
    157
    Sorry but you are confusing me.

    I am recording the d.o.b. in the CDateOfBirth field.

    The current age as at the date I look at the record is calculating correctly from what I see using the formula:
    =DateDiff("yyyy",[CDateOfBirth],Date()) in the CAge field.

    I am simply (although is is not simple for me) trying to copy the Age which is calculated in to the CAge field into the CAgeAtTOIR field, if there is no age already entered in the CAgeAtTOIR field.

    As can be seen from my questions, more detailed help would be of assistance.

  4. #4
    Join Date
    May 2002
    Posts
    157
    I have relooked and retested the formula for finding the Age of a client and have to thank 'pootle flump' for getting me to look at the formula.

    Thanks to MSOffice's "Calculating age from a birthdate in Access" (2003) I was able to work out the following formula:
    =DateDiff("yyyy",[CDateOfBirth],Now())+Int(Format(Now(),"mmdd")<Format([CDateOfBirth],"mmdd"))

    I found that if I added the above formula to the unbound text box CAge in the Control Source it calculates the correct age.

    If I then add the following as an 'On Exit' event of CAge, I can then get the Bound text box called CAgeAtTOIR to record the age if the CAgeAtTOIR is Null.

    **********************************************

    Private Sub CAge_Exit(Cancel As Integer)

    On Error GoTo Err_CAge_Exit

    If IsNull(Me![CAgeAtTOIR]) Then Me![CAgeAtTOIR].Value = DateDiff("yyyy", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))

    Exit_CAge_Exit:
    Exit Sub

    Err_CAge_Exit:
    MsgBox Err.Description
    Resume Exit_CAge_Exit

    End Sub

  5. #5
    Join Date
    May 2002
    Posts
    157

    recording age DateDiff with years and months

    I have the following which works quite well when only needing the age in years:
    If IsNull(Me![CAgeAtTOIR]) Then Me![CAgeAtTOIR].Value = DateDiff("yyyy", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))

    I know the following works if I only want to record the age in months:
    If IsNull(Me![CAgeAtTOIR]) Then Me![CAgeAtTOIR].Value = DateDiff("m", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))

    I can not work out how to get the answer in years and months, for example
    a person born on 01/01/2011 (and today's date is 6/2/2011) result in 0.01
    a person born on 01/01/2010 (and today's date is 6/2/2011) result in 1.01
    a person born on 01/02/2010 (and today's date is 6/2/2011) result in 1.00
    a person born on 01/08/2010 (and today's date is 6/2/2011) result in 0.06
    a person born on 01/03/2010 (and today's date is 6/2/2011) result in 0.11

    (results given in years and months)

    Can this be done?

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Karen Day View Post
    ...I know the following works if I only want to record the age in months:
    If IsNull(Me![CAgeAtTOIR]) Then Me![CAgeAtTOIR].Value = DateDiff("m", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))
    Once you have the calculated the number of Months it is easy to parse it into years and months left over:

    Years = Months\12

    MonthsLeftOver = Format(Months mod 12, "00" )

    Note that in 'Years = Months\12' the slash is a back slash! This returns the Remainder Only in a division operation.

    Then, to get your format like 1.01, you'd take the above variables, Years and MonthsLeftOver, and go something like

    YearsAndMonths = Years & "." & MonthsLeftOver

    Linq ;0)>
    Last edited by Missinglinq; 02-05-11 at 20:44.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    May 2002
    Posts
    157
    Hi there,

    Thanks for the information. I tried to get what you suggested to work but could not. In the end, using some of what you suggested and ended up with the following which does work.

    ********************************
    Dim totalmonths As Integer
    Dim monthsremaining As Integer
    Dim years As Integer

    If IsNull(Me![CAgeAtTOIR]) Then

    'Ensure that the field containing the result (eg CAgeAtTOIR) is formatted as a TEXT field within the table as the concatenation of the two variables and the "." turns the result into text.
    'Time in whole years
    years = DateDiff("yyyy", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))
    'Time difference in whole months
    totalmonths = DateDiff("m", [CDateOfBirth], Now()) + Int(Format(Now(), "mmdd") < Format([CDateOfBirth], "mmdd"))

    monthsremaining = totalmonths - (years * 12)
    Me.CAgeAtTOIR.Value = years & "." & monthsremaining
    End If

    ******************
    thanks again for your 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
  •