This control, =nz([TotalPaid],0) , produces #Error?

[Ed K]

Why would the rpt control =nz([TotalPaid],0) produce #Error on one Access
report, but work correctly in another Access report in the same database,
using the identical table of data? In fact, the report in which this
expression produces #Error is a copy of the report in which it works
correctly.

 

[fredg]

Why would the rpt control =nz([TotalPaid],0) produce #Error on one Access
report, but work correctly in another Access report in the same database,
using the identical table of data? In fact, the report in which this
expression produces #Error is a copy of the report in which it works
correctly.

Make sure the NAME of this control is not "TotalPaid".

 

[Ed K]

It was "TotalPaid" at one time. Now, what ever I change the name of the
control to, say "TotalPd", after running the report, the formula for the
control changes itself to include the name of the control (in this example,
=nz([TotalPd],0).

So, you are correct, but now the control formula updates itself to the new
name of the control, and I still get #Error displayed on the report for this
control.

Can you think of something else. BTW, when I just put in =TotalPd, the
report either lists the number in the field, or is blank, relfecting that
there is no data. I need it to display $0.

Thanks again.

Why would the rpt control =nz([TotalPaid],0) produce #Error on one Access
report, but work correctly in another Access report in the same database,
using the identical table of data? In fact, the report in which this
expression produces #Error is a copy of the report in which it works
correctly.

Make sure the NAME of this control is not "TotalPaid".

 

[Ed K]

Ooops, I should have typed =TotalPaid in the very last sentence of remark
below ("Can you think . . . ").

It was "TotalPaid" at one time. Now, what ever I change the name of the
control to, say "TotalPd", after running the report, the formula for the
control changes itself to include the name of the control (in this example,
=nz([TotalPd],0).

So, you are correct, but now the control formula updates itself to the new
name of the control, and I still get #Error displayed on the report for this
control.

Can you think of something else. BTW, when I just put in =TotalPd, the
report either lists the number in the field, or is blank, relfecting that
there is no data. I need it to display $0.

Thanks again.

Why would the rpt control =nz([TotalPaid],0) produce #Error on one Access
report, but work correctly in another Access report in the same database,
using the identical table of data? In fact, the report in which this
expression produces #Error is a copy of the report in which it works
correctly.

Make sure the NAME of this control is not "TotalPaid".

 

[fredg]

It was "TotalPaid" at one time. Now, what ever I change the name of the
control to, say "TotalPd", after running the report, the formula for the
control changes itself to include the name of the control (in this example,
=nz([TotalPd],0).

So, you are correct, but now the control formula updates itself to the new
name of the control, and I still get #Error displayed on the report for this
control.

Can you think of something else. BTW, when I just put in =TotalPd, the
report either lists the number in the field, or is blank, relfecting that
there is no data. I need it to display $0.

Thanks again.

Why would the rpt control =nz([TotalPaid],0) produce #Error on one Access
report, but work correctly in another Access report in the same database,
using the identical table of data? In fact, the report in which this
expression produces #Error is a copy of the report in which it works
correctly.

Make sure the NAME of this control is not "TotalPaid".

Aha!
Sounds like your database's NameAutoCorrect is not turned off.
Access will #error if the name of the control is the same as the name
of a field used in it's control source expression (it gets confused).

A quick fix would be to simply delete this one control and then add a
new UNBOUND countrol. Access will name it something like Text2.
Set it's control source to
=nz([TotalPaid],0)
As this control is not named "TotalPaid" it should work without
#error.

But you may still have future naming problems.
To resolve those future problems ....

Create a new database. Turn off Name Autocorrect.

Tools + Options + General + Name Autocorrect.

Import everything from your existing database.
Change the name of this control.

See Allen Browne's web site first for more information on
NameAutoCorrect.

http://www.allenbrowne.com/bug-03.html

Note... this Name AutoCorrect is not the same as the AutoCorrect
Options (to correct spelling errors) that shows on the Tool menu.

 

[Ed K]

Bummer, I had tried that. In fact, I had added a new control directly under
the control giving me a problem, and let Access name the control (e.g.,
Text97). With just TimePaid in control, report displays either a dollar
amount or a blank.

When I edit this control to either =nz([TotalPaid],0), or
=iif(isnull([TotalPaid]),0,[TotalPaid]), the report prints #Error, and
changed TotalPaid to Text97. It also change TotalPaid to Text97 on every
other control on the report that referenced TotalPaid (report totals, for
example).

I will try your other suggestion of changing Autocorrect.

It was "TotalPaid" at one time. Now, what ever I change the name of the
control to, say "TotalPd", after running the report, the formula for the
control changes itself to include the name of the control (in this example,
=nz([TotalPd],0).

So, you are correct, but now the control formula updates itself to the new
name of the control, and I still get #Error displayed on the report for this
control.

Can you think of something else. BTW, when I just put in =TotalPd, the
report either lists the number in the field, or is blank, relfecting that
there is no data. I need it to display $0.

Thanks again.

On Fri, 29 Aug 2008 13:18:00 -0700, Ed K wrote:

Why would the rpt control =nz([TotalPaid],0) produce #Error on one Access
report, but work correctly in another Access report in the same database,
using the identical table of data? In fact, the report in which this
expression produces #Error is a copy of the report in which it works
correctly.

Make sure the NAME of this control is not "TotalPaid".

Aha!
Sounds like your database's NameAutoCorrect is not turned off.
Access will #error if the name of the control is the same as the name
of a field used in it's control source expression (it gets confused).

A quick fix would be to simply delete this one control and then add a
new UNBOUND countrol. Access will name it something like Text2.
Set it's control source to
=nz([TotalPaid],0)
As this control is not named "TotalPaid" it should work without
#error.

But you may still have future naming problems.
To resolve those future problems ....

Create a new database. Turn off Name Autocorrect.

Tools + Options + General + Name Autocorrect.

Import everything from your existing database.
Change the name of this control.

See Allen Browne's web site first for more information on
NameAutoCorrect.

http://www.allenbrowne.com/bug-03.html

Note... this Name AutoCorrect is not the same as the AutoCorrect
Options (to correct spelling errors) that shows on the Tool menu.

 

[Ed K]

fredg,

Great advise on the AutoCorrect. That fixed the problem, and no need to
import any data. Here’s the fix: Turn off the AutoCorrect functions: (1)
Track name AutoCorrect info, and (2) Perform name AutoCorrect. (In Office
2007, click onthe top left button, select Access Options, select Current
Database, and scroll down to AutoCorrect and deselect the two options. Then,
cose, then reopen database.

Now it all works.

Thanks very much.

It was "TotalPaid" at one time. Now, what ever I change the name of the
control to, say "TotalPd", after running the report, the formula for the
control changes itself to include the name of the control (in this example,
=nz([TotalPd],0).

So, you are correct, but now the control formula updates itself to the new
name of the control, and I still get #Error displayed on the report for this
control.

Can you think of something else. BTW, when I just put in =TotalPd, the
report either lists the number in the field, or is blank, relfecting that
there is no data. I need it to display $0.

Thanks again.

On Fri, 29 Aug 2008 13:18:00 -0700, Ed K wrote:

Why would the rpt control =nz([TotalPaid],0) produce #Error on one Access
report, but work correctly in another Access report in the same database,
using the identical table of data? In fact, the report in which this
expression produces #Error is a copy of the report in which it works
correctly.

Make sure the NAME of this control is not "TotalPaid".

Aha!
Sounds like your database's NameAutoCorrect is not turned off.
Access will #error if the name of the control is the same as the name
of a field used in it's control source expression (it gets confused).

A quick fix would be to simply delete this one control and then add a
new UNBOUND countrol. Access will name it something like Text2.
Set it's control source to
=nz([TotalPaid],0)
As this control is not named "TotalPaid" it should work without
#error.

But you may still have future naming problems.
To resolve those future problems ....

Create a new database. Turn off Name Autocorrect.

Tools + Options + General + Name Autocorrect.

Import everything from your existing database.
Change the name of this control.

See Allen Browne's web site first for more information on
NameAutoCorrect.

http://www.allenbrowne.com/bug-03.html

Note... this Name AutoCorrect is not the same as the AutoCorrect
Options (to correct spelling errors) that shows on the Tool menu.

 

[tina]

fyi, Fred's advice - to start with a new database, turn off Name
Autocorrect immediately and then import all the objects from the "old"
database - is sound advice. Name Autocorrect has a way of "haunting" a
database even after it's been turned off; it's usually easier and safer in
the long run just to get a fresh start immediately.

hth

fredg,

Great advise on the AutoCorrect. That fixed the problem, and no need to
import any data. Here's the fix: Turn off the AutoCorrect functions: (1)
Track name AutoCorrect info, and (2) Perform name AutoCorrect. (In Office
2007, click onthe top left button, select Access Options, select Current
Database, and scroll down to AutoCorrect and deselect the two options. Then,
cose, then reopen database.

Now it all works.

Thanks very much.

It was "TotalPaid" at one time. Now, what ever I change the name of the
control to, say "TotalPd", after running the report, the formula for the
control changes itself to include the name of the control (in this example,
=nz([TotalPd],0).

So, you are correct, but now the control formula updates itself to the new
name of the control, and I still get #Error displayed on the report for this
control.

Can you think of something else. BTW, when I just put in =TotalPd, the
report either lists the number in the field, or is blank, relfecting that
there is no data. I need it to display $0.

Thanks again.

:

On Fri, 29 Aug 2008 13:18:00 -0700, Ed K wrote:

Why would the rpt control =nz([TotalPaid],0) produce #Error on one Access
report, but work correctly in another Access report in the same database,
using the identical table of data? In fact, the report in which this
expression produces #Error is a copy of the report in which it works
correctly.

Make sure the NAME of this control is not "TotalPaid".

Aha!
Sounds like your database's NameAutoCorrect is not turned off.
Access will #error if the name of the control is the same as the name
of a field used in it's control source expression (it gets confused).

A quick fix would be to simply delete this one control and then add a
new UNBOUND countrol. Access will name it something like Text2.
Set it's control source to
=nz([TotalPaid],0)
As this control is not named "TotalPaid" it should work without
#error.

But you may still have future naming problems.
To resolve those future problems ....

Create a new database. Turn off Name Autocorrect.

Tools + Options + General + Name Autocorrect.

Import everything from your existing database.
Change the name of this control.

See Allen Browne's web site first for more information on
NameAutoCorrect.

http://www.allenbrowne.com/bug-03.html

Note... this Name AutoCorrect is not the same as the AutoCorrect
Options (to correct spelling errors) that shows on the Tool menu.

 

[Ed K]

Ok, Tina, thanks. I'll do what Fred suggests.

Ed

fyi, Fred's advice - to start with a new database, turn off Name
Autocorrect immediately and then import all the objects from the "old"
database - is sound advice. Name Autocorrect has a way of "haunting" a
database even after it's been turned off; it's usually easier and safer in
the long run just to get a fresh start immediately.

hth

fredg,

Great advise on the AutoCorrect. That fixed the problem, and no need to
import any data. Here's the fix: Turn off the AutoCorrect functions: (1)
Track name AutoCorrect info, and (2) Perform name AutoCorrect. (In Office
2007, click onthe top left button, select Access Options, select Current
Database, and scroll down to AutoCorrect and deselect the two options. Then,
cose, then reopen database.

Now it all works.

Thanks very much.

On Fri, 29 Aug 2008 14:27:01 -0700, Ed K wrote:

It was "TotalPaid" at one time. Now, what ever I change the name of the
control to, say "TotalPd", after running the report, the formula for the
control changes itself to include the name of the control (in this example,
=nz([TotalPd],0).

So, you are correct, but now the control formula updates itself to the new
name of the control, and I still get #Error displayed on the report for this
control.

Can you think of something else. BTW, when I just put in =TotalPd, the
report either lists the number in the field, or is blank, relfecting that
there is no data. I need it to display $0.

Thanks again.

:

On Fri, 29 Aug 2008 13:18:00 -0700, Ed K wrote:

Why would the rpt control =nz([TotalPaid],0) produce #Error on one Access
report, but work correctly in another Access report in the same database,
using the identical table of data? In fact, the report in which this
expression produces #Error is a copy of the report in which it works
correctly.

Make sure the NAME of this control is not "TotalPaid".
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Aha!
Sounds like your database's NameAutoCorrect is not turned off.
Access will #error if the name of the control is the same as the name
of a field used in it's control source expression (it gets confused).

A quick fix would be to simply delete this one control and then add a
new UNBOUND countrol. Access will name it something like Text2.
Set it's control source to
=nz([TotalPaid],0)
As this control is not named "TotalPaid" it should work without
#error.

But you may still have future naming problems.
To resolve those future problems ....

Create a new database. Turn off Name Autocorrect.

Tools + Options + General + Name Autocorrect.

Import everything from your existing database.
Change the name of this control.

See Allen Browne's web site first for more information on
NameAutoCorrect.

http://www.allenbrowne.com/bug-03.html

Note... this Name AutoCorrect is not the same as the AutoCorrect
Options (to correct spelling errors) that shows on the Tool menu.

 

[tina]

you're welcome

Ok, Tina, thanks. I'll do what Fred suggests.

Ed

fyi, Fred's advice - to start with a new database, turn off Name
Autocorrect immediately and then import all the objects from the "old"
database - is sound advice. Name Autocorrect has a way of "haunting" a
database even after it's been turned off; it's usually easier and safer in
the long run just to get a fresh start immediately.

hth

fredg,

Great advise on the AutoCorrect. That fixed the problem, and no need to
import any data. Here's the fix: Turn off the AutoCorrect functions: (1)
Track name AutoCorrect info, and (2) Perform name AutoCorrect. (In Office
2007, click onthe top left button, select Access Options, select Current
Database, and scroll down to AutoCorrect and deselect the two options. Then,
cose, then reopen database.

Now it all works.

Thanks very much.

:

On Fri, 29 Aug 2008 14:27:01 -0700, Ed K wrote:

It was "TotalPaid" at one time. Now, what ever I change the name

of
the

control to, say "TotalPd", after running the report, the formula

for
the

control changes itself to include the name of the control (in this example,
=nz([TotalPd],0).

So, you are correct, but now the control formula updates itself to

the
new

name of the control, and I still get #Error displayed on the

report
for this

control.

Can you think of something else. BTW, when I just put in

=TotalPd,
the

report either lists the number in the field, or is blank,

relfecting
that

there is no data. I need it to display $0.

Thanks again.

:

On Fri, 29 Aug 2008 13:18:00 -0700, Ed K wrote:

Why would the rpt control =nz([TotalPaid],0) produce #Error on

one
Access

report, but work correctly in another Access report in the same database,
using the identical table of data? In fact, the report in which this
expression produces #Error is a copy of the report in which it works
correctly.

Make sure the NAME of this control is not "TotalPaid".
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


Aha!
Sounds like your database's NameAutoCorrect is not turned off.
Access will #error if the name of the control is the same as the name
of a field used in it's control source expression (it gets confused).

A quick fix would be to simply delete this one control and then add a
new UNBOUND countrol. Access will name it something like Text2.
Set it's control source to
=nz([TotalPaid],0)
As this control is not named "TotalPaid" it should work without
#error.

But you may still have future naming problems.
To resolve those future problems ....

Create a new database. Turn off Name Autocorrect.

Tools + Options + General + Name Autocorrect.

Import everything from your existing database.
Change the name of this control.

See Allen Browne's web site first for more information on
NameAutoCorrect.

http://www.allenbrowne.com/bug-03.html

Note... this Name AutoCorrect is not the same as the AutoCorrect
Options (to correct spelling errors) that shows on the Tool menu.