Coming up with a formula to calculate age. (1 Viewer)

fletchee2003

Registered User.
Local time
Today, 14:50
Joined
Apr 21, 2005
Messages
31
I have built a table with a field name of Birth date and have applied an input mask to have the data shown as: ##/##/##. Now, I would like to have another field labeled age. Is there a formula that I can create to have access calculate this in my table? I have tried "=mydate()-"Date Of Birth"" in the validation rule under the properties of the Age field; however, I keep getting an error. Any suggestions?
 

Mile-O

Back once again...
Local time
Today, 19:50
Joined
Dec 10, 2002
Messages
11,316
You can't put formulas into a database table. That sort of thing is for spreadsheets.

You would add a calculated field in a query or on within an unbound control on a form.

Also, you'll get a proper age formula somewhere in the Code Repository forum.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:50
Joined
Feb 19, 2002
Messages
42,973
The BirthDate field needs to be defined as a Date data type NOT a string. Do not use a Mask on the field. It will be nothing but annoying. Plus, the mask you have chosen will not allow data to be input properly. When entering data into a BirthDate field, you really need to use 4-digit years to avoid issues with Access making assumptions you won't be happy with. For example, if you wanted to enter a birth date of 01/01/1929, you wouldn't be able to since you have restricted the year to 2-digits. Don't forget, Access makes assumptions about which century a year is in. Years 00-29 are assumed to be in the 21st century. Years 30-99 are assumed to be in the 20th century. It is best to actually see the full year to prevent issues.

FYI, masks on dates are more annoying than helpful. If you don't use a mask, you can take advantage of Access' ability to complete dates. For example, if you enter 5/3 - Access will assume the current year and fill that in for you. When you use a mask, you must enter the complete date. There is no editing advantage either. If your field is defined as date, Access won't let you type letters into it and it won't let you enter an invalid date. What more do you want?
 

Users who are viewing this thread

Top Bottom