#Name? error on form

[Chuck Smith]

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!

 

[Al Campagna]

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]

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

 

[John Spencer]

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
'====================================================

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

 

[Al Campagna]

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."

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

 

[Ken Sheridan]

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

 

[Al Campagna]

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."

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."

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

 

[Chuck Smith]

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

 

[Chuck Smith]

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]

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
'====================================================

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

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
'====================================================