Calculate person's age based on the person's birth year and a table showing current year.

[DPrugh]

If I use =2016-[BirthYr] it works, but I don't want the users to go into the design view on the form to change the year.


The user may change the CurrentYr in a table called 'Current Semester'.
There must be a way to use the Year function. Any ideas?

[ssanfu]

You can use:

=Year(Date())- [BirthYr]


but it is not really accurate if my birthday is in November.....

[Missinglinq]

Simply subtracting the Year of Birth from the current year is not necessarily going to give you an accurate age! As Steve suggested, that will only work if the person has already had their birthday for the current year!

Here are three Formulas commonly used for an accurate calculation like this:

DateDiff("yyyy", [DOB], Date) - IIf(Format$(Date, "mmdd") < Format$([DOB], "mmdd"), 1, 0)

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

Linq ;0)>

[DPrugh]

I don't know the date - only the year
I tried Steve's idea, but I get a #Name? error

[Missinglinq]

...I don't know the date - only the year...

Then you can't reliably calculate a person's age!

Linq ;0)>

[DPrugh]

These people are >65 yrs old, so we are only interested in +/- a year.

[ssanfu]

Oops, I need to learn how to read slower.... or comprehend faster. Or both.

The user may change the CurrentYr in a table called 'Current Semester'.

In a table named "Current Semester" you have a field named "CurrentYr" that holds a year? it is a number?
And users can change the year?

Is there only one record with a year in it?
If not, how do you know which record is the year?

Maybe:

=Dlookup("CurrentYr", "[Current Semester]", "WhichRec = 'CurrentYear'") - [BirthYr]

where the table name is "Current Semester", the field that has the year is named "CurrentYr" and there is a column named "WhichRec" that is a text field with "CurrentYear" as the value to find.


Edit: What is the structure of table "Current Semester"?


BTW, shouldn't use spaces in object names.......

[DPrugh]

I'll try again: Structure
Sem 162
Prev_Sem 154
Prior_Sem 152
CurrentYr 2016

[DPrugh]

It's the 4th record in the table

[ssanfu]

Does every record have 2016 in the field "CurrentYr"??

How would I know which record to get the year from??

[DPrugh]

It's the 4th record in the table
The form for this person shows BirthYr
The next field: Age is calculated by reading the 4th record in the CurrentSemester table - BirthYr

[ssanfu]

The problem is that a table has no inherent order. Just because a record is the 4th record right now, doesn't mean it will be the 4th record tomorrow or the next day.
To be able to select a specific record, there needs to be something specific that can be found that says "This is the correct record to use. Now get the value from the field named 'CurrentYr' ".

Maybe you could post your dB???

[DPrugh]

The CurrentYr (2016) will always be in the 4th record until I change it to 2017.

[ssanfu]

Read Post #16 again.
Then read it again.

A table HAS NO ORDER. Until you can tell me to find a specific record using data in a field, I can't help any more.

For example, if table had a field for "userID", and you said "Get the "CurrentYr" where the userID = 22", that can be done. But not "Get the "CurrentYr" in the 4th record", because the record might not be the 4th record tomorrow..... if/when you do a "Compact and Repair", the record might not be the 4th record.


Good luck with your project.

[June7]

As has been pointed out - need parameter based on data to identify record.

Your data structure example appears to have two fields.
Sem 162
Prev_Sem 154
Prior_Sem 152
CurrentYr 2016

field1	field2
Sem	162
Prev_Sem	154
Prior_Sem	152
CurrentYr	2016

=DLookup("field2", "[Current Semester]", "field1 = 'CurrentYr'") - [BirthYr]

If this is not the structure then please provide example dataset.