Calculate Age field

[tjones]

I am attempting to calculate the age in years. I added the field to the form after I split the database into a Server/Client format. I am using 2010.

I placed this into the Control field

txtDOB = Date of birth (bound field)
txtAge = Unbound (does not have a field in the table)

=DateDiff("y",Year([txtDOB]),Year(Date()))

Thinking this should actually be a query and have that as the control source but not sure how to write that up in sql.

 

[John Big Booty]

Simply put your formula in the Control Source of your unbound field.

You might also find this link a useful resource.

 

[tjones]

I did add it to the control source. I get #Name? instead of an age.

I did look at that link which is where I got the formula for the control source.

 

[boblarson]

Make sure you don't have a control with the same name as the field you are using in the formula.

Also, the age can be calculated like:
=DateDiff("yyyy",[Date of Birth],Now())+Int(Format(Now(),"mmdd")<Format([Date of Birth],"mmdd"))

Where Date of Birth is the field name so substitute your field name. No other function is necessary and this will give you the correct age if someone hasn't had their birthday this year yet.

 

[John Big Booty]

The discussion here may also be of interest to you.