Determine Age - Access Developers - Computer Learning Zone


	Back to Access Developers Comments List

Determine Age

Brent Davis

2 years ago

Need some much needed assistance on a personal DB that I am working on. This is a DB that I am using to learn and practice mainly as I am not a programmer at all. My issue: I have a form for a personal family directory called PersonF with a subform named EventF. The PersonF has a PersonID, FirstName, LastName, Address, DOB, Calculated age field (PersonAge). The EventF has a combobox with a value list of "Birth", "Death",and others, Date for the event in the Combobox, and Calculated Age at the time of the event(EventAge). Now, the PersonAge field calculates correctly and the EventAge calculates correctly. But..If a person has died I would like the PersonAge to stop aging. Currently it just continues, person died at age 59 but PersonAge shows 76(age they would be if still alive). What I would like to be able to do is if there is a death event the PersonAge would reflect the same age as the EventAge. I have tried an IIF statement in the PersonAge text box and a DLookup. I can get the IIF statement to work as long as the death event is selected in the subform but if it is on another Event it reverts back to the continuous PersonAge. I want the PersonAge to stop at the death event without having to be on the death event on the EventF. This is the IIF statement that will give the proper PersonAge as long as the EventF is on the Death Event; =IIf([Forms]![PersonF]![EventF].[Form]![Event]="Death",[Forms]![PersonF]![EventF].[Form]![EventAge]). I could not get the DLookup to work at all.

I am sure this can be done but I have tried the Google machine to no avail! Any assistance is appreciated.

	Brent Davis OP @Reply 2 years ago
Wrong IIF statement entered above. The correct IIF statement is =IIf([Forms]![PersonF]![EventF].[Form]![Event]="Death",[Forms]![PersonF]![EventF].[Form]![EventAge],DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")))

	Alex Hedley @Reply 2 years ago
Calculate Age

	Alex Hedley @Reply 2 years ago
Age, Part 2

Adam Schwanz @Reply

2 years ago

Use a query for calculations instead of calculated fields if you can.

You need to check for if there is a record with Death in it, using Dlookup or Dcount. The way you have it now it's only checking the current selected record, which is why it works for the death event but not the others.

Something like this

=IIf(NZ(DCOUNT("*","EventT","PersonID=" & PersonID & " and Event='Death'),0)>0,[Forms]![PersonF]![EventF].[Form]![EventAge],DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")

	Brent Davis OP @Reply 2 years ago
Thanks Adam and Alex! I will attack it this evening!!

Brent Davis OP @Reply

2 years ago

All - not sure if this is the best way to do this but it works. Here is the code I came up with:

Details

Private Sub Form_Current()



    On Error Resume Next

    DoCmd.GoToControl "EventF"

    DoCmd.GoToControl "Event"

    DoCmd.FindRecord "Death"

    

    If EventF!Event = "Death" Then

        PersonAge = EventF!EventAge

    Else

        PersonAge = DateDiff("yyyy", [DOB], Now()) + Int(Format(Now(), "mmdd") < Format([DOB], "mmdd"))

    End If

    

    DoCmd.GoToControl "PersonF"

    

End Sub

Thanks for all of your thoughts and assistance!

	Richard Rost @Reply 2 years ago
If it works, it's right. Lol

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developers.

Next Unseen