Need Help - Showing students age

[Willy Makit]

I am somewhat new to this newsgroup, I'm hoping some of you may be able to
assist me. I have a access database that list all my students names and
information. But I would like it to display each students age (in years and
months) by comparing the student's Date Of Birth with the system's date. I
have a field for the students' date of birth. Is there anyway that I can
get the form to display the students' year (example: 9 years) and the month
(example: 4 months). I appreciate any assistance that anybody can offer, I
know the basics of Access, so I would appreciate it if information can be as
specific as possible. Please post any questions if you need more
information.

 

[Allen Browne]

This link has a function that illustrates how to return a more complete
DateDiff() string:
http://members.rogers.com/douglas.j.steele/Diff2Dates.html

 

[Steve Schapel]

Willy,

In the Control Source of an unbound textbox on your form, try this...

=(DateDiff("m",[Date Of Birth],Date())+(Day([Date Of
Birth])>Day(Date())))\12 & " years " & (DateDiff("m",[Date Of
Birth],Date())+(Day([Date Of Birth])>Day(Date()))) Mod 12 & " months"

 

[Willy Makit]

I appreciate your assistance, however I get back an odd response. In the
field box it displays "#Name?". What did I do wrong?
Also will this allow me to use the numbers in reports and calculations
(example: average age of a class)?

 

[Willy Makit]

I appreciate your assistance, however I get back an odd response. In the
field box it displays "#Name?". What did I do wrong?
Also will this allow me to use the numbers in reports and calculations
(example: average age of a class)?

 

[Steve Schapel]

Willy,

I have tested the expression I gave you, and it is correct. It assumes
that Date Of Birth is the name of the field, that this field is a
Date/Time data type, and that it is included in the record source of the
form. It also assumes that it is *not* the name of the textbox into
which you are typing the expresision... I guess this is the most likely
cause of the problem.

The answer to your second question is No, not directly. This type of
expression results in a text string, which can not then be used as the
basis of any numerical functions. If you want to do mathematical
processing, you have to do it at the point where you are dealing with
numerical values. For example, to get the average age of class, you
could do this... in the query that your form is based on, make a
calculated field like this, to return the age in months (a numerical
value)...
MonthAge: DateDiff("m",[Date Of Birth],Date())+(Day([Date Of
Birth])>Day(Date()))
.... And then, on your form, you could put this in the Control Source of
an unbound textbox in the Detail section...
=[MonthAge]\12 & " years " & [MonthAge] Mod 12 & " months"
.... and this in the Control Source of an unbound textbox in the form
Footer section...
=Avg([MonthAge])\12 & " years " & CInt(Avg([MonthAge]) Mod 12) & " months"