Apox Age query

[Hank153]

In my cemetery database I'm trying to calculate the approximite age at time of death.



Often, all I have is the year of birth or death, sometimes the month and year, other time nothing is given. Since I can't format to any standard date format with the info given, I'm using the Right function in a query to extract the year of birth and death (if known). Birthyear: Right([Birthdate],4) and DeathYear: Right([DeathDate],4). Then in the query I have a column APOX_AGE: NZ([DeathYear],0)-NZ([BirthYear],0)

I know I can't get an exact age but I do get an approximite age most of the time. The idea is to give me the approximite age at death or something to indicate it is not known. It seems to work OK if all the info is normal and/or given.
I need a way to handle the abnormal and not given events. i.e. If I don't have a date of birth or death, I would like the query to return a question mark (?).

Another problem is if the person was born and died in the same year. In this event I want to return ">1" and rather than 0.

I have not been able to come up with the proper IIF statements to make it work. Maybe there is an easier way.
Thanks for any help you can offer

[orange]

See the Year function, and I think you mean <1 not >1.
http://www.techonthenet.com/access/f.../date/year.php

[Hank153]

Thanks, I understand the year function i think. As I said in my question, I do not always have a proper date format. Often all I have is blank, 1/1800 or Jan1800 or before1800. So extracting the year function from that is not likely. However, in almost every case the year is contained in the last 4 digits of the cell - so the Right function will get the year for me.

The question is not so much about extracting the year as it is how to use it after it is extracted. You are right I did mean "less than 1"

[alansidman]

Try this in an expression in a new field in your query

IIF(DateOfDeath = Null or DateOfBirth = Null,"?",DateOfDeath - DateOfBirth)

[June7]

Cannot use: = Null

Review http://allenbrowne.com/casu-12.html

With true date values try:
IIf(IsNull(DateOfDeath) Or IsNull(DateOfBirth),"?",DateOfDeath - DateOfBirth)

However, that date calc will return days, not years. Use DateDiff function to return units other than days.

Maybe you want:
IIf(IsNull(DeathDate) Or IsNull(BirthDate), "?", IIf(Right([DeathDate],4) = Right([BirthDate],4), "<1", Right([DeathDate],4) - Right([BirthDate],4)))

[Hank153]

Thanks

APOX_AGE: IIf([DeathYear]=Null Or [BirthYear]=Null,"?",[DeathYear]-[BirthYear]) works OK. It seems a little strange I don't get the "?" when the DeathYear or BirthYear is not entered - The field is left blank. Maybe because it is a number format. But, I can live with that it there is no easy fix.

I still need the (less than 1) when DeathYear and BirthYear are the same. Any idea how this could be done?

[June7]

Review Post # 5.

[Hank153]

Thank you very much! It worked great.