If client dies, empy calculated age field

[Leen]

Hello,



In my Access 2010 DB, I am almost done but there is a layout question: If a client passes away, it looks kind of stupid to have the age field continue to be calculated. (Age: 113, etc.)
I have tried several options, but so far no luck.

Here's what I have:

Checkbox = yes, date field is filled with date of passing (Date)
Age = text box calculated, based on DOB (Date)

How do I code: if client is deceased, empty age (calculated) field?

Thanks very much for helping me...

[ssanfu]

It depends on how you are calculating the age. Are you using a function?

A function could be like:

Age = CalcAge(DOB, TheDate)

where
DOB = Date of Birth
TheDate = the date at which you want to calculate the age (ie today, last month, etc)


So for today, it would be

Age = CalcAge(DOB,Date())

It could be modified to check if the person passed:

Age = CalcAge(DOB,IIf(CheckBoxName,DoD,Date()))

where DoD = date of death

Change CheckBoxName to the name of your checkbox
and DoD to the name of the text box that has the date of passing

[Missinglinq]

Keeping in mind that the Age Field, being a calculated Field, should be Unbound. Now, a couple of things:

Replacing the following Controls, in the code, below, with the actual names of your Controls

• Age (Unbound, calculated Control for displaying age)
• ckbDeceased (your Checkbox)
• DOB (Date of Birth)
• DOD (Date of Death)

Next, there are several different formulas for calculating the age of someone on a given date; in the code, below, I've used the following

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

You can use this or replace it with your own code. Then you can use this code:

Private Sub ckbDeceased_AfterUpdate()
 If Me.ckbDeceased = -1 Then
  Me.DOD = Date
  Me.Age = DateDiff("yyyy", [DOB], [DOD]) + Int(Format([DOD], "mmdd") < Format([DOB], "mmdd"))
 Else
  Me.DOD = Null
  Me.Age = DateDiff("yyyy", [DOB], Date) + Int(Format(Date, "mmdd") < Format([DOB], "mmdd"))
 End If
End Sub

You'll need to place this same exact code in the Form_Current event, in order for the calculated Age to populate the Age Control, as you move from Record-to-Record.

Linq ;0)>

[Leen]

Keeping in mind that the Age Field, being a calculated Field, should be Unbound. Now, a couple of things:

Replacing the following Controls, in the code, below, with the actual names of your Controls

• Age (Unbound, calculated Control for displaying age)
• ckbDeceased (your Checkbox)
• DOB (Date of Birth)
• DOD
• (Date of Death)

Next, there are several different formulas for calculating the age of someone on a given date; in the code, below, I've used the following

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

You can use this or replace it with your own code. Then you can use this code:

Private Sub ckbDeceased_AfterUpdate()
 If Me.ckbDeceased = -1 Then
  Me.DOD = Date
  Me.Age = DateDiff("yyyy", [DOB], [DOD]) + Int(Format([DOD], "mmdd") < Format([DOB], "mmdd"))
 Else
  Me.DOD = Null
  Me.Age = DateDiff("yyyy", [DOB], Date) + Int(Format(Date, "mmdd") < Format([DOB], "mmdd"))
 End If
End Sub

You'll need to place this same exact code in the Form_Current event, in order for the calculated Age to populate the Age Control, as you move from Record-to-Record.

Linq ;0)>

Hello,

Thanks for your response!

This what I have in the form: =DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

Hopefully this makes things a bit clearer.

Thanks,

Leen

[Missinglinq]

That's the same Expression I've used for Age; I just used the Date() function, instead of Now(). The Now() function includes the Time component, and should only be used when the Time component is actually needed.

The code I posted, once you've replaced my Control names with your actual Control names, should do just fine. Remember to place the code in the Form_Current event, as well.

Linq ;0)>

[Leen]

That's the same Expression I've used for Age; I just used the Date() function, instead of Now(). The Now() function includes the Time component, and should only be used when the Time component is actually needed.

The code I posted, once you've replaced my Control names with your actual Control names, should do just fine. Remember to place the code in the Form_Current event, as well.

Linq ;0)>

Hello again,

I have been very busy; that's why it took a while.
Thank you very much for helping me. It is really appreciated.

I want to double-check though, because I am not sure how to go from here.

My original code is placed in properties, under the "Age" field, in Control Source, under the Data tab.
Do I have to delete this code?

And I am not sure how to put your code in a Function.
Here it is: Age=CalcAge(DOB,IIF(Deceased,DeceasedDate, Date()))

Also the same situation with the Form Current event. There is already something occupying that line; so I am not sure how to go from here.
You help (step-by-step would be great) enormously appreciated!

Thanks,

Leen

[Leen]

That's the same Expression I've used for Age; I just used the Date() function, instead of Now(). The Now() function includes the Time component, and should only be used when the Time component is actually needed.

The code I posted, once you've replaced my Control names with your actual Control names, should do just fine. Remember to place the code in the Form_Current event, as well.

Linq ;0)>

Hi there,

Maybe a not so smart question; I want the deceased date to stay, but when checked, the age field should become empty when client dies. Is that the case in your code, or...?

Thanks for answering,

Leen

[Missinglinq]

…when checked, the age field should become empty when client dies…

Sorry, I missed that! Most apps like this leave the Age Field populated with the age at death, but I’ll show you how to set it to Null (empty), in the code below.

…My original code is placed in properties, under the "Age" field, in Control Source, under the Data tab.

…Do I have to delete this code…

...same situation with the Form Current event. There is already something occupying that line; so I am not sure how to go from here...

Yes; your requirement to set the Age Field to Null (empty) means that you cannot do this using the Age Field’s Control Source. When a Control Source Property has anything in it other than the a Field Name, the Field cannot be populated by having data entered into it through the keyboard or through the use of code. Your need to have Age set to Null (empty) on the client’s death means that you have to populate Age from somewhere else.

Also, when you need to do several things using a given event, like Form_Current, you simply add one after the other in that Sub. You do not have two Sub Form_Currents!

So, delete the Control Source for the Age Field. Then use the following codes:

Private Sub ckbDeceased_AfterUpdate()

 If Me.ckbDeceased = -1 Then
  Me.DOD = Date
  Me.Age = Null
 Else
  Me.DOD = Null
  Me.Age = DateDiff("yyyy", [DOB], Date) + Int(Format(Date, "mmdd") < Format([DOB], "mmdd"))
 End If

End Sub

Private Sub DOB_AfterUpdate()

 If Nz(Me.DOB, "") <> "" Then
  Me.Age = DateDiff("yyyy", [DOB], Date) + Int(Format(Date, "mmdd") < Format([DOB], "mmdd"))
 Else
  Me.Age = Null
 End If

End Sub

Private Sub Form_Current()
 
'Enter your current code for this event here

 If Me.ckbDeceased = -1 Then
  Me.Age = Null
 Else
  Me.Age = DateDiff("yyyy", [DOB], Date) + Int(Format(Date, "mmdd") < Format([DOB], "mmdd"))
 End If

End Sub

As I said before, in the above code you need to use your actual names for the Controls named DOB, DOD, ckbDeceased and Age.

Linq ;0)>

[Leen]

Dear MissingLinq,

Thank you very much for all the work you put in for me; it is truly appreciated!
I followed everything you said to the letter: deleted the Age field "control source", and put all three subs in the respective locations, without receiving any error.

However, an error occurred when trying to run the DB: The expression on Current you entered as the event property setting produced the following error: Ambiguous name detected: Form_Current

Also after the error, the DB starts up, but with an empty Age field, although the client did not die in this particular record.

Thank you beforehand for looking into this!

Leen

[Missinglinq]

...an error occurred when trying to run the DB: The expression on Current you entered as the event property setting produced the following error: Ambiguous name detected: Form_Current...

This would indicate that you ignored this part of my post:

...when you need to do several things using a given event, like Form_Current, you simply add one after the other in that Sub. You do not have two Sub Form_Currents!

and that you do, indeed, have two or more separate Form_Current subs! The message "Ambiguous name detected" means that there are multiple subs named Form_Current and the Access Gnomes don't know which of them to execute.

Once again, you need to have all code that needs to be in the Form_Current event in a single Sub of that name. In the Code Module for the Form you should have

Private Sub Form_Current()
 
'Enter your current code for this event here

 If Me.ckbDeceased = -1 Then
  Me.Age = Null
 Else
  Me.Age = DateDiff("yyyy", [DOB], Date) + Int(Format(Date, "mmdd") < Format([DOB], "mmdd"))
 End If

End Sub

In the OnCurrent Property (in the Property Pane) you should only see [Event Procedure], nothing else.

Linq ;0)>

[Leen]

Your indication is right. Sorry, but I missed that part. I wondered how one could indeed have 2 Form_Current events; this is called miscommunication on my part.
However, it works like a charm!

Thank you, thank you.
It helped me al lot, plus I learned a lot.

Thanks again!

Leen

[Missinglinq]

...It helped me a lot, plus I learned a lot...

That's why we're here!

Good luck with your project!

Linq ;0)>