A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating age in a form



 
 
Thread Tools Display Modes
  #1  
Old July 14th, 2009, 07:01 PM posted to microsoft.public.access.forms
tm3025
external usenet poster
 
Posts: 11
Default Calculating age in a form

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.
  #2  
Old July 14th, 2009, 07:41 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Calculating age in a form

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" wrote in message
...
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.



  #3  
Old July 14th, 2009, 08:46 PM posted to microsoft.public.access.forms
tm3025
external usenet poster
 
Posts: 11
Default Calculating age in a form

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.

"Jeff Boyce" wrote:

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" wrote in message
...
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.




  #4  
Old July 14th, 2009, 09:02 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Calculating age in a form

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

"tm3025" wrote in message
...
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.

"Jeff Boyce" wrote:

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" wrote in message
...
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.






  #5  
Old September 20th, 2009, 11:43 PM posted to microsoft.public.access.forms
Sync-opy
external usenet poster
 
Posts: 10
Default Calculate Chronological Age ex: 10yr 4mo 2days old

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
  #6  
Old September 21st, 2009, 12:48 AM posted to microsoft.public.access.forms
fredg
external usenet poster
 
Posts: 4,386
Default Calculate Chronological Age ex: 10yr 4mo 2days old

On Sun, 20 Sep 2009 15:43:02 -0700, Sync-opy wrote:

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())

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
  #7  
Old September 21st, 2009, 06:03 AM posted to microsoft.public.access.forms
Sync-opy
external usenet poster
 
Posts: 10
Default Calculate Chronological Age ex: 10yr 4mo 2days old

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!

"fredg" wrote:

On Sun, 20 Sep 2009 15:43:02 -0700, Sync-opy wrote:

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())

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

  #8  
Old September 21st, 2009, 06:59 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Calculate Chronological Age ex: 10yr 4mo 2days old

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

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?

--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.