|
|
|
date: Wed, 10 Sep 2008 14:02:21 -0700,
group: microsoft.public.access.gettingstarted
back
Re: #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 news:e0axEi4EJHA.1456@TK2MSFTNGP03.phx.gbl...
> 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!
date: Wed, 10 Sep 2008 18:19:41 -0400
author: Al Campagna
Re: #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
date: Wed, 10 Sep 2008 18:02:33 -0700
author: Chuck Smith
Re: #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
date: Thu, 11 Sep 2008 10:06:14 -0400
author: John Spencer
Re: #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
news:%23pG5So6EJHA.1272@TK2MSFTNGP05.phx.gbl...
> 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
date: Thu, 11 Sep 2008 10:29:12 -0400
author: Al Campagna
RE: #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!
>
date: Thu, 11 Sep 2008 10:42:01 -0700
author: Ken Sheridan
Re: #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
news:OeZhDrBFJHA.3288@TK2MSFTNGP03.phx.gbl...
> 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
> news:%23pG5So6EJHA.1272@TK2MSFTNGP05.phx.gbl...
>> 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
>
>
date: Thu, 11 Sep 2008 18:35:20 -0400
author: Al Campagna
Re: #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)
date: Fri, 12 Sep 2008 16:14:28 -0700
author: Chuck Smith
Re: #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
> '====================================================
date: Fri, 12 Sep 2008 16:31:15 -0700
author: Chuck Smith
Re: #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
>> '====================================================
date: Sat, 13 Sep 2008 10:18:20 -0400
author: John Spencer
|
|
