Coding problem.

[cheyanne]

I have attached an image of a form (see Dates) and have a problem which I am unable to resolve.
On the form I have a field txtBirthdate (where the date of birth is entered i.e. "La fecha del nacimiento".
Currently this works fine and uses the formula =(Date()-[BirthDate])/365.25 in the txtAge field (Edad) to calculate the age,
format set to 1 decimal place.

The problem I have is that animals sadly die and to that end I have a check box (default set to FALSE). If an animal dies
this check box (chkDeceased) is ticked to read TRUE, and a previously invisible text box( txtDateDied), formatted to short date appears.
Their date of death is entered here. What I need is for Date() to cease updating the record when the chkDeceased check box is set to True
and to calculate the animals age at death and retain it, rather than what happens at present i.e. in a years time the dead animal will be
a year older. Thier "age at death" can be important for statistical and research purposes. On the attached form you can see that Solo died
on 17th May 2009, having a date of birth of 1st April 2007. Whe he sadly died, of cancer, he was just over 2 years old but today his record
says that he is 5.1 years old.

I recently realised that the deceased animal is effectively "alive", as far as the txtAge field is concerned, which is clearly not only embarrassing
but inaccurate. I tried coding a simple If, Else, End if for example

If chkDeceased = -1) Then
txtAge = (txtDateDied - txtBirthdate)/365.26
Else
txtAge = (Date()-[Birthdate])/365.25
End If

(Having removed the previous control source for txtAge)

I tried placing this code on the Forms OnCurrent event, then on the AfterUpdate évent of txtDateDied but nothing
seems to work. In Tab order terms txtBirthdate is set before txtDateDied.


My questions are

a. Is the above code correct to produce what I seek?

b. If the code is correct, then where would it be best placed as an event?

c. If this is a possibility, can I presume I am correct to remove the control source on txtAge?

c. In general has anybody any ideas as to how I can make this work?



Many thanks for reading my post and I would much appreciate any assistance you could offer me.

Regards

Cheyanne

[recyan]

Have you tried putting this in the txtAge field in the Form :

=iif(chkDeceased = -1, (txtDateDied - txtBirthdate) / 365.25, (Date() - txtBirthdate) / 365.25)

Thanks

[cheyanne]

Hi Recyan

Many thanks for your reply. No I had not tried that particular line of code, nor did I think of it.

I am not sure exactly what it does, but shall now endeavour to find out exactly what it does for future reference.

I have a good idea what it does but have never used the "iif" statement before.

Without you guys (and gals) life would be so much more difficult for those of us learning Access.
Forums, such as this, are an indespensible form of the Internet.

Upon receipt of your reply I immediately made your code the source control of the txtAge field and it worked like a dream.

Solo's age, at his death, now reads as being 2.1 years, which is correct, as opposed to the 5.1 years it read earlier this morning, which was totally incorrect.

I shall now mark the thread as resolved thanks to your assistance.

Many thanks again.


Cheyanne

[recyan]

Glad you found it helpful.
Note : Even I am learning.

Thanks