Calculating age in a form

[tm3025]

I entered the following expression into the control source of an unbound text
box on a form. The form was created from a personal info table which DOB came
from, but Date of X came from a different table. When I try to enter this it,
all I get in the text box is #NAME?

=DateDiff("yyyy", [DOB], Surgery![Date of
X])-IIF(Format([DOB],"mmdd")>Format([X!Date of X],"mmdd"),1,0)

My first attempt at this involved a query with the same expression, and it
returned correct values. However, when I made the field from the query the
control source of the text box in the form, it also returned #NAME?

I'm guessing there must be something wrong in the way I am going about this.
Any help would be much appreciated.

 

[Jeff Boyce]

When Access says "#NAME", its telling you it doesn't recognize the name you
gave it. Typically this is because the name of the form or the name of the
control isn't recognized.

I'd look first at Format([X!Date of X] ... is "X" a table name or a form
name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tm3025]

Sorry it should [Surgery!Date of Surgery]... Surgery is a table and Date of
Surgery is a field in that table. Why is it recognizing it in a query, but
not in a form? Does it have to do with the fact that I created this form from
a different table than Surgery? Thanks for your help.

When Access says "#NAME", its telling you it doesn't recognize the name you
gave it. Typically this is because the name of the form or the name of the
control isn't recognized.

I'd look first at Format([X!Date of X] ... is "X" a table name or a form
name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I entered the following expression into the control source of an unbound
text
box on a form. The form was created from a personal info table which DOB
came
from, but Date of X came from a different table. When I try to enter this
it,
all I get in the text box is #NAME?

=DateDiff("yyyy", [DOB], Surgery![Date of
X])-IIF(Format([DOB],"mmdd")>Format([X!Date of X],"mmdd"),1,0)

My first attempt at this involved a query with the same expression, and it
returned correct values. However, when I made the field from the query the
control source of the text box in the form, it also returned #NAME?

I'm guessing there must be something wrong in the way I am going about
this.
Any help would be much appreciated.

 

[Jeff Boyce]

When you are referring to a field in a table from within a form, you either
need to have that table as part of the source for the form, or you need to
use something like the DLookup() function to "see" that value.

If you are referring to a control on a form (from within a form), you need
to tell Access to look in that form, with something like:
Forms!YourSourceForm!YourControl

The simpler version of this happens when you refer to a control on the SAME
form as you're in. Then you can use:
Me!YourControl

Check Access HELP for exact syntax on these.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry it should [Surgery!Date of Surgery]... Surgery is a table and Date
of
Surgery is a field in that table. Why is it recognizing it in a query, but
not in a form? Does it have to do with the fact that I created this form
from
a different table than Surgery? Thanks for your help.

When Access says "#NAME", its telling you it doesn't recognize the name
you
gave it. Typically this is because the name of the form or the name of
the
control isn't recognized.

I'd look first at Format([X!Date of X] ... is "X" a table name or a form
name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I entered the following expression into the control source of an unbound
text
box on a form. The form was created from a personal info table which
DOB
came
from, but Date of X came from a different table. When I try to enter
this
it,
all I get in the text box is #NAME?

=DateDiff("yyyy", [DOB], Surgery![Date of
X])-IIF(Format([DOB],"mmdd")>Format([X!Date of X],"mmdd"),1,0)

My first attempt at this involved a query with the same expression, and
it
returned correct values. However, when I made the field from the query
the
control source of the text box in the form, it also returned #NAME?

I'm guessing there must be something wrong in the way I am going about
this.
Any help would be much appreciated.

 

[Sync-opy]

Currently, the function I have in the 'Age' unbound control of my form is
(thx Fred):
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)
This works great for correctly displaying a persons age in years...

....However, I have spent days playing with this and many other posted
functions and codes, and have been unable to accomplish what I am really
looking for.

What I would really like is a way to automatically display a person's
precise Chronological Age in Years, Months and Days.
For example:
If I have a someone's data entered into my [DOB] field as:
05/18/1999
and then given today's current date of:
09/20/2009
I would like to have my the unbound control 'Age' in my form field display
"10 yrs, 4 months, 2 days"

I appreciate any and all help!! Please keep in mind I am relatively new in
the world of Access!

Thanks!

sync-opy

 

[fredg]

Currently, the function I have in the 'Age' unbound control of my form is
(thx Fred):
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)
This works great for correctly displaying a persons age in years...

...However, I have spent days playing with this and many other posted
functions and codes, and have been unable to accomplish what I am really
looking for.

What I would really like is a way to automatically display a person's
precise Chronological Age in Years, Months and Days.
For example:
If I have a someone's data entered into my [DOB] field as:
05/18/1999
and then given today's current date of:
09/20/2009
I would like to have my the unbound control 'Age' in my form field display
"10 yrs, 4 months, 2 days"

I appreciate any and all help!! Please keep in mind I am relatively new in
the world of Access!

Thanks!

sync-opy

See:
http://www.accessmvp.com/djsteele/Diff2Dates.html

=Diff2Dates("ymd",[DOB],Date())

 

[Sync-opy]

Thank you for the assist. I am not sure how to use the code at the link. I
am new to the world of access. How do I get Date1 to pull information from
my [DOB] field for each record? How do I associate the code to the form
field to display age?

I know these are rookie questions but I have been unable to figure this out
(and I am a rookie).

Thanks again!

Currently, the function I have in the 'Age' unbound control of my form is
(thx Fred):
=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)
This works great for correctly displaying a persons age in years...

...However, I have spent days playing with this and many other posted
functions and codes, and have been unable to accomplish what I am really
looking for.

What I would really like is a way to automatically display a person's
precise Chronological Age in Years, Months and Days.
For example:
If I have a someone's data entered into my [DOB] field as:
05/18/1999
and then given today's current date of:
09/20/2009
I would like to have my the unbound control 'Age' in my form field display
"10 yrs, 4 months, 2 days"

I appreciate any and all help!! Please keep in mind I am relatively new in
the world of Access!

Thanks!

sync-opy

See:
http://www.accessmvp.com/djsteele/Diff2Dates.html

=Diff2Dates("ymd",[DOB],Date())

 

[John W. Vinson]

On Sun, 20 Sep 2009 22:03:02 -0700, Sync-opy

Create a new Module on the modules tab. Copy and paste the code from the
website into the module; save it as "basDates" - actually any name *EXCEPT*
Diff2Dates.

To call it, create a Query based on your table. In a vacant Field cell of the
query type:

ChronoAge: Diff2Dates("ymd", [DOB], Date())

Base your form or report on this query.

Thank you for the assist. I am not sure how to use the code at the link. I
am new to the world of access. How do I get Date1 to pull information from
my [DOB] field for each record? How do I associate the code to the form
field to display age?