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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|