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?
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?
You can use:
=Year(Date())- [BirthYr]
but it is not really accurate if my birthday is in November.....
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:
Code:DateDiff("yyyy", [DOB], Date) - IIf(Format$(Date, "mmdd") < Format$([DOB], "mmdd"), 1, 0)
Code:DateDiff("yyyy", [DOB], Date()) + Int( Format(Date(), "mmdd") < Format([DOB], "mmdd") )
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
I don't know the date - only the year
I tried Steve's idea, but I get a #Name? error
These people are >65 yrs old, so we are only interested in +/- a year.
Oops, I need to learn how to read slower.... or comprehend faster. Or both.
In a table named "Current Semester" you have a field named "CurrentYr" that holds a year? it is a number?The user may change the CurrentYr in a table called 'Current Semester'.
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:
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.Code:=Dlookup("CurrentYr", "[Current Semester]", "WhichRec = 'CurrentYear'") - [BirthYr]
Edit: What is the structure of table "Current Semester"?
BTW, shouldn't use spaces in object names.......
I'll try again: Structure
Sem 162
Prev_Sem 154
Prior_Sem 152
CurrentYr 2016
It's the 4th record in the table
Does every record have 2016 in the field "CurrentYr"??
How would I know which record to get the year from??
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
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???
Last edited by ssanfu; 01-22-2016 at 01:48 PM.
The CurrentYr (2016) will always be in the 4th record until I change it to 2017.
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.
Last edited by June7; 01-22-2016 at 05:00 PM.
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.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.