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
|
|||
|
|||
#Name? error on form
I'm fairly new to Access (but have been in computer tech support,
including database support, for over 20 years). I'm using Access 2002 SP3. I'm troubleshooting a problem where I get the #Name? error on a form. I've created a stripped-down scenario and can duplicate the problem consistently: Table1 has the following three fields: PrimaryKey (AutoNumber) Name (Text) DOB (Date/Time) [Date of Birth] Query1 is to provide calculated fields for various forms & reports, and currently contains only 1 field: AgeToday (AgeToday: DateDiff("y",[DOB],Now())/365.25) The AgeToday calculated field works fine in the Query datasheet. I created "Form1" (AutoForm: Columnar) based on Table1. In the form's Design View I then added an Unbound text box. For its Control Source I used the Expression Builder to come up with the following: =Query1!AgeToday When I view Form1, I get #Name? where the AgeToday value should be. What am I doing wrong? Thanks, ....Chuck P.S. I've got to say that I think Access is probably the most infuriating program I've used in my entire IT career, and I'm not the kind of person who infuriates easily! |
#2
|
|||
|
|||
#Name? error on form
Chuck Smith,
First, don't name a field [Name]. "Name" is a reserved word in Access. Try FullName, or something like that. Also, the Age calculation should be DateDiff("d",[DOB],Now())/365.25) (using "d" instead of "y") Not sure why you're using a query to calculate AgeToday. An unbound text control (named AgeToday) with the following calculation will always display the age... =DateDiff("d",[DOB],Now())/365.25) Try that simpler solution, and avoid the "#Name" problem altogether. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Chuck Smith" ""chucksmithg\"@gZZZ[remove the Zs]mail.com" wrote in message ... I'm fairly new to Access (but have been in computer tech support, including database support, for over 20 years). I'm using Access 2002 SP3. I'm troubleshooting a problem where I get the #Name? error on a form. I've created a stripped-down scenario and can duplicate the problem consistently: Table1 has the following three fields: PrimaryKey (AutoNumber) Name (Text) DOB (Date/Time) [Date of Birth] Query1 is to provide calculated fields for various forms & reports, and currently contains only 1 field: AgeToday (AgeToday: DateDiff("y",[DOB],Now())/365.25) The AgeToday calculated field works fine in the Query datasheet. I created "Form1" (AutoForm: Columnar) based on Table1. In the form's Design View I then added an Unbound text box. For its Control Source I used the Expression Builder to come up with the following: =Query1!AgeToday When I view Form1, I get #Name? where the AgeToday value should be. What am I doing wrong? Thanks, ...Chuck P.S. I've got to say that I think Access is probably the most infuriating program I've used in my entire IT career, and I'm not the kind of person who infuriates easily! |
#3
|
|||
|
|||
#Name? error on form
Thanks for the speedy reply, Al.
First, don't name a field [Name]. "Name" is a reserved word in Access. Try FullName, or something like that. Thanks for catching that. Actually, my "production" database was using "NameLF" and still having the problem, so that wasn't the cause of this issue (but to be sure, I changed my test DB field name to "NameLF" and that didn't fix it). It seems like what I did should be working. Also, the Age calculation should be DateDiff("d",[DOB],Now())/365.25) (using "d" instead of "y") Thanks for this too. According to Access Help, "d" is "Day" and "y" is "Day of year." I wonder what the subtle distinction is, although it doesn't seem to matter in this calculation as I get the same number when I use either one. Not sure why you're using a query to calculate AgeToday. An unbound text control (named AgeToday) with the following calculation will always display the age... I've been trying to read a couple of books on Access, and I think at least one advised putting calculated fields in queries, especially if you want to use it in more than one place (rather than have to re-create the unbound text control every time on each new form & report that uses it). I thought (but could be wrong) that I'd also read about "issues" with calculated fields being more likely to update correctly when created in a query as opposed to on a form or report. In my case, I will want to use "AgeToday" in more than one form and/or report. What's the best way to create it once and then re-use it? Thanks again, ....Chuck |
#4
|
|||
|
|||
#Name? error on form
I would probably create a little VBA function to calculate age.
Public Function fAge(dtmDOB, Optional dtmDate) 'Returns the Age in years, for dtmDOB. 'Age calculated as of dtmDate, or as of today if dtmDate is missing. If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then set to today's date If IsDate(dtmDOB) Then 'If date passed, then calculate age fAge = DateDiff("yyyy", dtmDOB, dtmDate) + (DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) dtmDate) Else fAge = Null End If End Function Optionally you can use these expressions in a query or as the source of a control on a form or report 'Fails if DOB is null CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) 'Returns Null if DOB is Null CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") Format(Date(),"mmdd")) '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Chuck Smith wrote: Thanks for the speedy reply, Al. First, don't name a field [Name]. "Name" is a reserved word in Access. Try FullName, or something like that. Thanks for catching that. Actually, my "production" database was using "NameLF" and still having the problem, so that wasn't the cause of this issue (but to be sure, I changed my test DB field name to "NameLF" and that didn't fix it). It seems like what I did should be working. Also, the Age calculation should be DateDiff("d",[DOB],Now())/365.25) (using "d" instead of "y") Thanks for this too. According to Access Help, "d" is "Day" and "y" is "Day of year." I wonder what the subtle distinction is, although it doesn't seem to matter in this calculation as I get the same number when I use either one. Not sure why you're using a query to calculate AgeToday. An unbound text control (named AgeToday) with the following calculation will always display the age... I've been trying to read a couple of books on Access, and I think at least one advised putting calculated fields in queries, especially if you want to use it in more than one place (rather than have to re-create the unbound text control every time on each new form & report that uses it). I thought (but could be wrong) that I'd also read about "issues" with calculated fields being more likely to update correctly when created in a query as opposed to on a form or report. In my case, I will want to use "AgeToday" in more than one form and/or report. What's the best way to create it once and then re-use it? Thanks again, ...Chuck |
#5
|
|||
|
|||
#Name? error on form
Chuck,
OK on the "NameLF" name. I would suggest that First and Last names should be separate fields, but that's your call. I got "confoosed" on the DateDiff syntax. I see now where "y" will yield the number of days between two dates. (I had never used that argument) I thought you were misusing the "yyyy" argument. But for the sake of clarity, I'd still prefer to use the "d" argument. Easier to read. But, that's your call too... I'll check on the distinction between "d" and "y", and post back on this thread later. The AgeToday calculated value can be done in the query behind a form or report, or on the form or report itself... as an unbound calculated field. Either is acceptable... but... usually I do the calc on the form, and on reports I use a "bound" calculated values (in the query) for easier Footer calculations. If done in the query, footer totals are much easier to derive. (ex. =Avg(AgeToday) in any footer would yield the average age) -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Chuck Smith" wrote in message ... Thanks for the speedy reply, Al. First, don't name a field [Name]. "Name" is a reserved word in Access. Try FullName, or something like that. Thanks for catching that. Actually, my "production" database was using "NameLF" and still having the problem, so that wasn't the cause of this issue (but to be sure, I changed my test DB field name to "NameLF" and that didn't fix it). It seems like what I did should be working. Also, the Age calculation should be DateDiff("d",[DOB],Now())/365.25) (using "d" instead of "y") Thanks for this too. According to Access Help, "d" is "Day" and "y" is "Day of year." I wonder what the subtle distinction is, although it doesn't seem to matter in this calculation as I get the same number when I use either one. Not sure why you're using a query to calculate AgeToday. An unbound text control (named AgeToday) with the following calculation will always display the age... I've been trying to read a couple of books on Access, and I think at least one advised putting calculated fields in queries, especially if you want to use it in more than one place (rather than have to re-create the unbound text control every time on each new form & report that uses it). I thought (but could be wrong) that I'd also read about "issues" with calculated fields being more likely to update correctly when created in a query as opposed to on a form or report. In my case, I will want to use "AgeToday" in more than one form and/or report. What's the best way to create it once and then re-use it? Thanks again, ...Chuck |
#6
|
|||
|
|||
#Name? error on form
You might also like to take a look at:
http://www.mvps.org/access/datetime/date0001.htm Of the methods given there I'm not too happy with the first one: Age=DateDiff("yyyy", [Bdate], Now())+ _ Int( Format(Now(), "mmdd") Format( [Bdate], "mmdd") ) as it relies on the implementation of Boolean values as -1 or 0. Its what the head of one software company of my acquaintance once termed 'being unduly chummy with the implementation'. I'd prefer: Age=DateDiff("yyyy", [Bdate], Now())- _ IIf(Format(Now(), "mmdd") Format( [Bdate], "mmdd"),1,0) Ken Sheridan Stafford, England "Chuck Smith" ""chucksmithg"@gZZZ[remov" wrote: I'm fairly new to Access (but have been in computer tech support, including database support, for over 20 years). I'm using Access 2002 SP3. I'm troubleshooting a problem where I get the #Name? error on a form. I've created a stripped-down scenario and can duplicate the problem consistently: Table1 has the following three fields: PrimaryKey (AutoNumber) Name (Text) DOB (Date/Time) [Date of Birth] Query1 is to provide calculated fields for various forms & reports, and currently contains only 1 field: AgeToday (AgeToday: DateDiff("y",[DOB],Now())/365.25) The AgeToday calculated field works fine in the Query datasheet. I created "Form1" (AutoForm: Columnar) based on Table1. In the form's Design View I then added an Unbound text box. For its Control Source I used the Expression Builder to come up with the following: =Query1!AgeToday When I view Form1, I get #Name? where the AgeToday value should be. What am I doing wrong? Thanks, ....Chuck P.S. I've got to say that I think Access is probably the most infuriating program I've used in my entire IT career, and I'm not the kind of person who infuriates easily! |
#7
|
|||
|
|||
#Name? error on form
Chuck,
From what I've been able to find out, The "y" works just like the "d" -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Al Campagna" wrote in message ... Chuck, OK on the "NameLF" name. I would suggest that First and Last names should be separate fields, but that's your call. I got "confoosed" on the DateDiff syntax. I see now where "y" will yield the number of days between two dates. (I had never used that argument) I thought you were misusing the "yyyy" argument. But for the sake of clarity, I'd still prefer to use the "d" argument. Easier to read. But, that's your call too... I'll check on the distinction between "d" and "y", and post back on this thread later. The AgeToday calculated value can be done in the query behind a form or report, or on the form or report itself... as an unbound calculated field. Either is acceptable... but... usually I do the calc on the form, and on reports I use a "bound" calculated values (in the query) for easier Footer calculations. If done in the query, footer totals are much easier to derive. (ex. =Avg(AgeToday) in any footer would yield the average age) -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Chuck Smith" wrote in message ... Thanks for the speedy reply, Al. First, don't name a field [Name]. "Name" is a reserved word in Access. Try FullName, or something like that. Thanks for catching that. Actually, my "production" database was using "NameLF" and still having the problem, so that wasn't the cause of this issue (but to be sure, I changed my test DB field name to "NameLF" and that didn't fix it). It seems like what I did should be working. Also, the Age calculation should be DateDiff("d",[DOB],Now())/365.25) (using "d" instead of "y") Thanks for this too. According to Access Help, "d" is "Day" and "y" is "Day of year." I wonder what the subtle distinction is, although it doesn't seem to matter in this calculation as I get the same number when I use either one. Not sure why you're using a query to calculate AgeToday. An unbound text control (named AgeToday) with the following calculation will always display the age... I've been trying to read a couple of books on Access, and I think at least one advised putting calculated fields in queries, especially if you want to use it in more than one place (rather than have to re-create the unbound text control every time on each new form & report that uses it). I thought (but could be wrong) that I'd also read about "issues" with calculated fields being more likely to update correctly when created in a query as opposed to on a form or report. In my case, I will want to use "AgeToday" in more than one form and/or report. What's the best way to create it once and then re-use it? Thanks again, ...Chuck |
#8
|
|||
|
|||
#Name? error on form
Thanks, Al (& John & Ken),
I too would greatly prefer to have First & Last names as separate fields, but I'm using imported data from other sources, and the name comes as "Last/First." (I know that routines could be written to fix that, but I'm trying hard to keep this difficult project as simple as possible.) I suspect your tip on when to use the bound vs. unbound control will come in handy. Filing that one away... ....Chuck Al Campagna wrote: Chuck, OK on the "NameLF" name. I would suggest that First and Last names should be separate fields, but that's your call. I got "confoosed" on the DateDiff syntax. I see now where "y" will yield the number of days between two dates. (I had never used that argument) I thought you were misusing the "yyyy" argument. But for the sake of clarity, I'd still prefer to use the "d" argument. Easier to read. But, that's your call too... I'll check on the distinction between "d" and "y", and post back on this thread later. The AgeToday calculated value can be done in the query behind a form or report, or on the form or report itself... as an unbound calculated field. Either is acceptable... but... usually I do the calc on the form, and on reports I use a "bound" calculated values (in the query) for easier Footer calculations. If done in the query, footer totals are much easier to derive. (ex. =Avg(AgeToday) in any footer would yield the average age) |
#9
|
|||
|
|||
#Name? error on form
Thanks John,
As I'm still learning Access, and especially trying to learn the "big picture" (good practices and why to use one method over another), could you please tell me why you'd choose the VBA function over putting a normal Access expression inside the unbound text control? Off the top of my (admittedly ignorant) head, the only advantage I can see would be to make it perhaps a little easier to re-use the age calculation (it appears I'd still have to add the unbound text control for each form or report that uses it, but at least I could just type in "=fAge" each time). The disadvantage seems to me to be adding the complexity of VBA (which I'm also rather inexperienced with). Thanks very much for taking the time for all this. ....Chuck John Spencer wrote: I would probably create a little VBA function to calculate age. Public Function fAge(dtmDOB, Optional dtmDate) 'Returns the Age in years, for dtmDOB. 'Age calculated as of dtmDate, or as of today if dtmDate is missing. If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then set to today's date If IsDate(dtmDOB) Then 'If date passed, then calculate age fAge = DateDiff("yyyy", dtmDOB, dtmDate) + (DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) dtmDate) Else fAge = Null End If End Function Optionally you can use these expressions in a query or as the source of a control on a form or report 'Fails if DOB is null CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) 'Returns Null if DOB is Null CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") Format(Date(),"mmdd")) '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === |
#10
|
|||
|
|||
#Name? error on form
The main advantage of using a function is that it is easy to call from
many different places. And once you have the function set up you don't have to remember how to calculate age each time you need to do so. Also, if you make a calculation mistake in the function it is easier to fix the mistake in one place rather than checking it every place you may have used it. And you can add an error handling routine to the function to handle unexpected things and return a default value of some type - such as -1 for age negative ages - someone posted a DOB in the future by accident and your entry controls did not catch the predicted birth. The expressions that I posted all use one or more VBA functions, so there is little difference in the execution. It does boil down to personal preference. '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === Chuck Smith wrote: Thanks John, As I'm still learning Access, and especially trying to learn the "big picture" (good practices and why to use one method over another), could you please tell me why you'd choose the VBA function over putting a normal Access expression inside the unbound text control? Off the top of my (admittedly ignorant) head, the only advantage I can see would be to make it perhaps a little easier to re-use the age calculation (it appears I'd still have to add the unbound text control for each form or report that uses it, but at least I could just type in "=fAge" each time). The disadvantage seems to me to be adding the complexity of VBA (which I'm also rather inexperienced with). Thanks very much for taking the time for all this. ...Chuck John Spencer wrote: I would probably create a little VBA function to calculate age. Public Function fAge(dtmDOB, Optional dtmDate) 'Returns the Age in years, for dtmDOB. 'Age calculated as of dtmDate, or as of today if dtmDate is missing. If Not IsDate(dtmDate) Then dtmDate = Date 'If as of date not passed then set to today's date If IsDate(dtmDOB) Then 'If date passed, then calculate age fAge = DateDiff("yyyy", dtmDOB, dtmDate) + (DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) dtmDate) Else fAge = Null End If End Function Optionally you can use these expressions in a query or as the source of a control on a form or report 'Fails if DOB is null CurrentAge = Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd")) 'Returns Null if DOB is Null CurrentAge =DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") Format(Date(),"mmdd")) '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === |
Thread Tools | |
Display Modes | |
|
|