Nz function not working

  • Thread starter Thread starter Mishanya
  • Start date Start date
M

Mishanya

In the MainForm there is an unbound box MainformTotal showing total from
Subform.
Its Control Source goes:
=[SubformName].[Form]![SubformTotalControlName]
and it works perfectly.
When the SubformTotalControlName is empty (no records in the Subform), the
MainformTotal control shows #Error.
I change its Control Source to:
=Nz([SubformName].[Form]![SubformTotalControlName])
but to no effect.
I even set the SubformTotalControl' Control Source to
=Nz(Sum([RelevantColumn]))
still to no effect.
Again, when not empty it all works - means the reference sintax is OK.
Can blame it on DB corruption (possibly - Nz function missing in my Access)
or there is more plausible explanation?
 
Nz works on values in a field. If there is no record, then there is no field
to examine.

You might try the following. I am not sure it will work reliably.
=IIf([sfrmFAQ].[Form].[RecordsetClone].[RecordCount]>0,
[SubformName].[Form]![SubformTotalControlName], 0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you for reply.
What's sfrmFAQ?

John Spencer said:
Nz works on values in a field. If there is no record, then there is no field
to examine.

You might try the following. I am not sure it will work reliably.
=IIf([sfrmFAQ].[Form].[RecordsetClone].[RecordCount]>0,
[SubformName].[Form]![SubformTotalControlName], 0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
In the MainForm there is an unbound box MainformTotal showing total from
Subform.
Its Control Source goes:
=[SubformName].[Form]![SubformTotalControlName]
and it works perfectly.
When the SubformTotalControlName is empty (no records in the Subform), the
MainformTotal control shows #Error.
I change its Control Source to:
=Nz([SubformName].[Form]![SubformTotalControlName])
but to no effect.
I even set the SubformTotalControl' Control Source to
=Nz(Sum([RelevantColumn]))
still to no effect.
Again, when not empty it all works - means the reference sintax is OK.
Can blame it on DB corruption (possibly - Nz function missing in my Access)
or there is more plausible explanation?
Click to expand...
Click to expand...
 
It works for me on test data, yoou are not telling the NZ function what to
return though.

See what

=NZ([SubformName].[Form]![SubformTotalControlName],0)

or

=NZ(=[SubformName].[Form]![SubformTotalControlName],"")

results in??? (If Null, the first should return 0 and the second "" (blank
Control))

But maybe there is a problem that I hve not seen because John is much more
in tune on Access than I am?????

Regards
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Mishanya said:
Thank you for reply.
What's sfrmFAQ?

John Spencer said:
Nz works on values in a field. If there is no record, then there is no field
to examine.

You might try the following. I am not sure it will work reliably.
=IIf([sfrmFAQ].[Form].[RecordsetClone].[RecordCount]>0,
[SubformName].[Form]![SubformTotalControlName], 0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
In the MainForm there is an unbound box MainformTotal showing total from
Subform.
Its Control Source goes:
=[SubformName].[Form]![SubformTotalControlName]
and it works perfectly.
When the SubformTotalControlName is empty (no records in the Subform), the
MainformTotal control shows #Error.
I change its Control Source to:
=Nz([SubformName].[Form]![SubformTotalControlName])
but to no effect.
I even set the SubformTotalControl' Control Source to
=Nz(Sum([RelevantColumn]))
still to no effect.
Again, when not empty it all works - means the reference sintax is OK.
Can blame it on DB corruption (possibly - Nz function missing in my Access)
or there is more plausible explanation?
Click to expand...
Click to expand...
Click to expand...
 
John says "Nz works on values in a field. If there is no record, then there
is no field
to examine" - this explains why I get #Error.
I just did not catch what "sfrmFAQ" means.

MikeJohnB said:
It works for me on test data, yoou are not telling the NZ function what to
return though.

See what

=NZ([SubformName].[Form]![SubformTotalControlName],0)

or

=NZ(=[SubformName].[Form]![SubformTotalControlName],"")

results in??? (If Null, the first should return 0 and the second "" (blank
Control))

But maybe there is a problem that I hve not seen because John is much more
in tune on Access than I am?????

Regards
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Mishanya said:
Thank you for reply.
What's sfrmFAQ?

John Spencer said:
Nz works on values in a field. If there is no record, then there is no field
to examine.

You might try the following. I am not sure it will work reliably.
=IIf([sfrmFAQ].[Form].[RecordsetClone].[RecordCount]>0,
[SubformName].[Form]![SubformTotalControlName], 0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Mishanya wrote:
In the MainForm there is an unbound box MainformTotal showing total from
Subform.
Its Control Source goes:
=[SubformName].[Form]![SubformTotalControlName]
and it works perfectly.
When the SubformTotalControlName is empty (no records in the Subform), the
MainformTotal control shows #Error.
I change its Control Source to:
=Nz([SubformName].[Form]![SubformTotalControlName])
but to no effect.
I even set the SubformTotalControl' Control Source to
=Nz(Sum([RelevantColumn]))
still to no effect.
Again, when not empty it all works - means the reference sintax is OK.
Can blame it on DB corruption (possibly - Nz function missing in my Access)
or there is more plausible explanation?
Click to expand...
Click to expand...
Click to expand...
 
Mishanya,

Do yourself a big favor and open the "Northwind database samples†supplied
with most versions of Access. Open the Orders form in design view, look at
how they calculate the order subtotal. Notice that it’s placed in the forms
footer then referenced on the main form. I used to have the same trouble you
describe with the NZ function until I studied the "orders form†now I don't
get the #error and my form calculates faster. Remember not to save this in a
table but calculate this on the fly. Hope this helps.

Richard


Mishanya said:
John says "Nz works on values in a field. If there is no record, then there
is no field
to examine" - this explains why I get #Error.
I just did not catch what "sfrmFAQ" means.

MikeJohnB said:
It works for me on test data, yoou are not telling the NZ function what to
return though.

See what

=NZ([SubformName].[Form]![SubformTotalControlName],0)

or

=NZ(=[SubformName].[Form]![SubformTotalControlName],"")

results in??? (If Null, the first should return 0 and the second "" (blank
Control))

But maybe there is a problem that I hve not seen because John is much more
in tune on Access than I am?????

Regards
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Mishanya said:
Thank you for reply.
What's sfrmFAQ?

:

Nz works on values in a field. If there is no record, then there is no field
to examine.

You might try the following. I am not sure it will work reliably.
=IIf([sfrmFAQ].[Form].[RecordsetClone].[RecordCount]>0,
[SubformName].[Form]![SubformTotalControlName], 0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Mishanya wrote:
In the MainForm there is an unbound box MainformTotal showing total from
Subform.
Its Control Source goes:
=[SubformName].[Form]![SubformTotalControlName]
and it works perfectly.
When the SubformTotalControlName is empty (no records in the Subform), the
MainformTotal control shows #Error.
I change its Control Source to:
=Nz([SubformName].[Form]![SubformTotalControlName])
but to no effect.
I even set the SubformTotalControl' Control Source to
=Nz(Sum([RelevantColumn]))
still to no effect.
Again, when not empty it all works - means the reference sintax is OK.
Can blame it on DB corruption (possibly - Nz function missing in my Access)
or there is more plausible explanation?
Click to expand...
Click to expand...
Click to expand...
 
Hi Richard.
Northwind was the example in the first place. There is simple
=Sum([ExtendedPrice]) in the subform total, then the name of its control is
referenced in the Mainform.
It works fine, but - before you place first entry in the subform, You always
get #Error, which I try to avoid. The reason - I have two subforms, totals of
wich I need to sum in the Mainform. When even one of subforms does not have
records, the sum of the two subform-totals gives #Error.


Richard said:
Mishanya,

Do yourself a big favor and open the "Northwind database samples†supplied
with most versions of Access. Open the Orders form in design view, look at
how they calculate the order subtotal. Notice that it’s placed in the forms
footer then referenced on the main form. I used to have the same trouble you
describe with the NZ function until I studied the "orders form†now I don't
get the #error and my form calculates faster. Remember not to save this in a
table but calculate this on the fly. Hope this helps.

Richard


Mishanya said:
John says "Nz works on values in a field. If there is no record, then there
is no field
to examine" - this explains why I get #Error.
I just did not catch what "sfrmFAQ" means.

MikeJohnB said:
It works for me on test data, yoou are not telling the NZ function what to
return though.

See what

=NZ([SubformName].[Form]![SubformTotalControlName],0)

or

=NZ(=[SubformName].[Form]![SubformTotalControlName],"")

results in??? (If Null, the first should return 0 and the second "" (blank
Control))

But maybe there is a problem that I hve not seen because John is much more
in tune on Access than I am?????

Regards
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


:

Thank you for reply.
What's sfrmFAQ?

:

Nz works on values in a field. If there is no record, then there is no field
to examine.

You might try the following. I am not sure it will work reliably.
=IIf([sfrmFAQ].[Form].[RecordsetClone].[RecordCount]>0,
[SubformName].[Form]![SubformTotalControlName], 0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Mishanya wrote:
In the MainForm there is an unbound box MainformTotal showing total from
Subform.
Its Control Source goes:
=[SubformName].[Form]![SubformTotalControlName]
and it works perfectly.
When the SubformTotalControlName is empty (no records in the Subform), the
MainformTotal control shows #Error.
I change its Control Source to:
=Nz([SubformName].[Form]![SubformTotalControlName])
but to no effect.
I even set the SubformTotalControl' Control Source to
=Nz(Sum([RelevantColumn]))
still to no effect.
Again, when not empty it all works - means the reference sintax is OK.
Can blame it on DB corruption (possibly - Nz function missing in my Access)
or there is more plausible explanation?
Click to expand...
Click to expand...
Click to expand...
 
Sorry

sFrmFaq would be the name of the subform control on the main form. The
name of the subform control is often the same as the name of the form
used in the subform control, BUT that is not necessarily true.

I quickly tested the solution in one of my databases and that was the
name of the subform control. It may or may not work since I forgot to
test it with no records being returned in the subform. Yeah, I know. I
certainly should have tested that condition.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thank you for reply.
What's sfrmFAQ?

John Spencer said:
Nz works on values in a field. If there is no record, then there is no field
to examine.

You might try the following. I am not sure it will work reliably.
=IIf([sfrmFAQ].[Form].[RecordsetClone].[RecordCount]>0,
[SubformName].[Form]![SubformTotalControlName], 0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
In the MainForm there is an unbound box MainformTotal showing total from
Subform.
Its Control Source goes:
=[SubformName].[Form]![SubformTotalControlName]
and it works perfectly.
When the SubformTotalControlName is empty (no records in the Subform), the
MainformTotal control shows #Error.
I change its Control Source to:
=Nz([SubformName].[Form]![SubformTotalControlName])
but to no effect.
I even set the SubformTotalControl' Control Source to
=Nz(Sum([RelevantColumn]))
still to no effect.
Again, when not empty it all works - means the reference sintax is OK.
Can blame it on DB corruption (possibly - Nz function missing in my Access)
or there is more plausible explanation?
Click to expand...
Click to expand...
Click to expand...
 
"sfrmFAQ" seems to be the name of the subform control in the example.
Perhaps John copied some existing code. Change it to the name of your
subform control.

Mishanya said:
John says "Nz works on values in a field. If there is no record, then
there
is no field
to examine" - this explains why I get #Error.
I just did not catch what "sfrmFAQ" means.

MikeJohnB said:
It works for me on test data, yoou are not telling the NZ function what
to
return though.

See what

=NZ([SubformName].[Form]![SubformTotalControlName],0)

or

=NZ(=[SubformName].[Form]![SubformTotalControlName],"")

results in??? (If Null, the first should return 0 and the second ""
(blank
Control))

But maybe there is a problem that I hve not seen because John is much
more
in tune on Access than I am?????

Regards
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Mishanya said:
Thank you for reply.
What's sfrmFAQ?

:

Nz works on values in a field. If there is no record, then there is
no field
to examine.

You might try the following. I am not sure it will work reliably.
=IIf([sfrmFAQ].[Form].[RecordsetClone].[RecordCount]>0,
[SubformName].[Form]![SubformTotalControlName], 0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Mishanya wrote:
In the MainForm there is an unbound box MainformTotal showing total
from
Subform.
Its Control Source goes:
=[SubformName].[Form]![SubformTotalControlName]
and it works perfectly.
When the SubformTotalControlName is empty (no records in the
Subform), the
MainformTotal control shows #Error.
I change its Control Source to:
=Nz([SubformName].[Form]![SubformTotalControlName])
but to no effect.
I even set the SubformTotalControl' Control Source to
=Nz(Sum([RelevantColumn]))
still to no effect.
Again, when not empty it all works - means the reference sintax is
OK.
Can blame it on DB corruption (possibly - Nz function missing in my
Access)
or there is more plausible explanation?
Click to expand...
Click to expand...
Click to expand...
 
God Bless You!
Finally I got it thanks to You!

John Spencer said:
Nz works on values in a field. If there is no record, then there is no field
to examine.

You might try the following. I am not sure it will work reliably.
=IIf([sfrmFAQ].[Form].[RecordsetClone].[RecordCount]>0,
[SubformName].[Form]![SubformTotalControlName], 0)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
In the MainForm there is an unbound box MainformTotal showing total from
Subform.
Its Control Source goes:
=[SubformName].[Form]![SubformTotalControlName]
and it works perfectly.
When the SubformTotalControlName is empty (no records in the Subform), the
MainformTotal control shows #Error.
I change its Control Source to:
=Nz([SubformName].[Form]![SubformTotalControlName])
but to no effect.
I even set the SubformTotalControl' Control Source to
=Nz(Sum([RelevantColumn]))
still to no effect.
Again, when not empty it all works - means the reference sintax is OK.
Can blame it on DB corruption (possibly - Nz function missing in my Access)
or there is more plausible explanation?
Click to expand...
Click to expand...
 
Back
Top