Null Value

[BarryC]

If a sql statment returns no records how do I have a text box express 0. The
"#error" comes up when no records are found.

 

[Duane Hookom]

Comes up where? Do you have a control source that you would like to share?
The normal method is to ignore any posts that suggest using Nz() since they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

 

[BarryC]

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in the footer. My
master report shows blank when the value is zero? Know any tricks? Thanks.

Barry

Comes up where? Do you have a control source that you would like to share?
The normal method is to ignore any posts that suggest using Nz() since they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text box express 0.
The
"#error" comes up when no records are found.

 

[Duane Hookom]

Tricks for what? Do you want to show something on your main report if there
are no records in the subreport? If so, try the IIf() function with a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in the footer.
My
master report shows blank when the value is zero? Know any tricks? Thanks.

Barry

Comes up where? Do you have a control source that you would like to
share?
The normal method is to ignore any posts that suggest using Nz() since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text box express
0.
The
"#error" comes up when no records are found.

 

[BarryC]

Yes, that is what I want to do. Where on the main report would I include the
IIF() function?

Tricks for what? Do you want to show something on your main report if there
are no records in the subreport? If so, try the IIf() function with a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in the footer.
My
master report shows blank when the value is zero? Know any tricks? Thanks.

Barry

Comes up where? Do you have a control source that you would like to
share?
The normal method is to ignore any posts that suggest using Nz() since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text box express
0.
The
"#error" comes up when no records are found.

 

[Duane Hookom]

In the control source where you want to display a total from the subreport
or a 0. It would be something like:
=IIf(sbrptA.Report.HasData,sbrptA.Report.txtSum,0)

--
Duane Hookom
MS Access MVP
--

Yes, that is what I want to do. Where on the main report would I include
the
IIF() function?

Tricks for what? Do you want to show something on your main report if
there
are no records in the subreport? If so, try the IIf() function with a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in the
footer.
My
master report shows blank when the value is zero? Know any tricks?
Thanks.

Barry

:

Comes up where? Do you have a control source that you would like to
share?
The normal method is to ignore any posts that suggest using Nz() since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text box
express
0.
The
"#error" comes up when no records are found.

 

[BarryC]

Duane,

Sorry, still not getting it. I have the IIF statement in my sub report. The
sub report works fine by itself. When I insert it into my main report it does
not show when the value is null. When there is a value in the sub report it
works fine.

In the control source where you want to display a total from the subreport
or a 0. It would be something like:
=IIf(sbrptA.Report.HasData,sbrptA.Report.txtSum,0)

--
Duane Hookom
MS Access MVP
--

Yes, that is what I want to do. Where on the main report would I include
the
IIF() function?

Tricks for what? Do you want to show something on your main report if
there
are no records in the subreport? If so, try the IIf() function with a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in the
footer.
My
master report shows blank when the value is zero? Know any tricks?
Thanks.

Barry

:

Comes up where? Do you have a control source that you would like to
share?
The normal method is to ignore any posts that suggest using Nz() since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text box
express
0.
The
"#error" comes up when no records are found.

 

[Duane Hookom]

The IIf() expression should be in the main report, not the subreport. Isn't
the main report where you are having the problem?

--
Duane Hookom
MS Access MVP

Duane,

Sorry, still not getting it. I have the IIF statement in my sub report.
The
sub report works fine by itself. When I insert it into my main report it
does
not show when the value is null. When there is a value in the sub report
it
works fine.

In the control source where you want to display a total from the
subreport
or a 0. It would be something like:
=IIf(sbrptA.Report.HasData,sbrptA.Report.txtSum,0)

--
Duane Hookom
MS Access MVP
--

Yes, that is what I want to do. Where on the main report would I
include
the
IIF() function?

:

Tricks for what? Do you want to show something on your main report if
there
are no records in the subreport? If so, try the IIf() function with a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in the
footer.
My
master report shows blank when the value is zero? Know any tricks?
Thanks.

Barry

:

Comes up where? Do you have a control source that you would like to
share?
The normal method is to ignore any posts that suggest using Nz()
since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text box
express
0.
The
"#error" comes up when no records are found.

 

[BarryC]

Yes, the main report is the problem. I have tried inserting a text box with
the controll source IIF(subRpt.HasData...) All I get is the #NAME? error
message.

The IIf() expression should be in the main report, not the subreport. Isn't
the main report where you are having the problem?

--
Duane Hookom
MS Access MVP

Duane,

Sorry, still not getting it. I have the IIF statement in my sub report.
The
sub report works fine by itself. When I insert it into my main report it
does
not show when the value is null. When there is a value in the sub report
it
works fine.

In the control source where you want to display a total from the
subreport
or a 0. It would be something like:
=IIf(sbrptA.Report.HasData,sbrptA.Report.txtSum,0)

--
Duane Hookom
MS Access MVP
--

Yes, that is what I want to do. Where on the main report would I
include
the
IIF() function?

:

Tricks for what? Do you want to show something on your main report if
there
are no records in the subreport? If so, try the IIf() function with a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in the
footer.
My
master report shows blank when the value is zero? Know any tricks?
Thanks.

Barry

:

Comes up where? Do you have a control source that you would like to
share?
The normal method is to ignore any posts that suggest using Nz()
since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text box
express
0.
The
"#error" comes up when no records are found.

 

[Duane Hookom]

Could you provide the exact expression you use as the control source? Did
you change the names in the expression to match your actual names?

--
Duane Hookom
MS Access MVP
--

Yes, the main report is the problem. I have tried inserting a text box
with
the controll source IIF(subRpt.HasData...) All I get is the #NAME? error
message.

The IIf() expression should be in the main report, not the subreport.
Isn't
the main report where you are having the problem?

--
Duane Hookom
MS Access MVP

Duane,

Sorry, still not getting it. I have the IIF statement in my sub report.
The
sub report works fine by itself. When I insert it into my main report
it
does
not show when the value is null. When there is a value in the sub
report
it
works fine.

:

In the control source where you want to display a total from the
subreport
or a 0. It would be something like:
=IIf(sbrptA.Report.HasData,sbrptA.Report.txtSum,0)

--
Duane Hookom
MS Access MVP
--

Yes, that is what I want to do. Where on the main report would I
include
the
IIF() function?

:

Tricks for what? Do you want to show something on your main report
if
there
are no records in the subreport? If so, try the IIf() function with
a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in the
footer.
My
master report shows blank when the value is zero? Know any
tricks?
Thanks.

Barry

:

Comes up where? Do you have a control source that you would like
to
share?
The normal method is to ignore any posts that suggest using Nz()
since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text box
express
0.
The
"#error" comes up when no records are found.

 

[BarryC]

=IIf([Reports]![steve current month1].HasData,[Reports]![steve current
month1]![countOfControllers],0)

This is the control source for the text box on the main report. The sub
report is named "steve current month1" and the text box on the sub report is
named "countofcontrollers".

Could you provide the exact expression you use as the control source? Did
you change the names in the expression to match your actual names?

--
Duane Hookom
MS Access MVP
--

Yes, the main report is the problem. I have tried inserting a text box
with
the controll source IIF(subRpt.HasData...) All I get is the #NAME? error
message.

The IIf() expression should be in the main report, not the subreport.
Isn't
the main report where you are having the problem?

--
Duane Hookom
MS Access MVP


Duane,

Sorry, still not getting it. I have the IIF statement in my sub report.
The
sub report works fine by itself. When I insert it into my main report
it
does
not show when the value is null. When there is a value in the sub
report
it
works fine.

:

In the control source where you want to display a total from the
subreport
or a 0. It would be something like:
=IIf(sbrptA.Report.HasData,sbrptA.Report.txtSum,0)

--
Duane Hookom
MS Access MVP
--

Yes, that is what I want to do. Where on the main report would I
include
the
IIF() function?

:

Tricks for what? Do you want to show something on your main report
if
there
are no records in the subreport? If so, try the IIf() function with
a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in the
footer.
My
master report shows blank when the value is zero? Know any
tricks?
Thanks.

Barry

:

Comes up where? Do you have a control source that you would like
to
share?
The normal method is to ignore any posts that suggest using Nz()
since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text box
express
0.
The
"#error" comes up when no records are found.

 

[Duane Hookom]

You messed up my expression by putting the cart in front of the horse
(report in front of subreport control name). Try:

=IIf([steve current month1].[Report].HasData,[steve current
month1].Report![countOfControllers],0)

--
Duane Hookom
MS Access MVP
--

=IIf([Reports]![steve current month1].HasData,[Reports]![steve current
month1]![countOfControllers],0)

This is the control source for the text box on the main report. The sub
report is named "steve current month1" and the text box on the sub report
is
named "countofcontrollers".

Could you provide the exact expression you use as the control source? Did
you change the names in the expression to match your actual names?

--
Duane Hookom
MS Access MVP
--

Yes, the main report is the problem. I have tried inserting a text box
with
the controll source IIF(subRpt.HasData...) All I get is the #NAME?
error
message.

:

The IIf() expression should be in the main report, not the subreport.
Isn't
the main report where you are having the problem?

--
Duane Hookom
MS Access MVP


Duane,

Sorry, still not getting it. I have the IIF statement in my sub
report.
The
sub report works fine by itself. When I insert it into my main
report
it
does
not show when the value is null. When there is a value in the sub
report
it
works fine.

:

In the control source where you want to display a total from the
subreport
or a 0. It would be something like:
=IIf(sbrptA.Report.HasData,sbrptA.Report.txtSum,0)

--
Duane Hookom
MS Access MVP
--

Yes, that is what I want to do. Where on the main report would I
include
the
IIF() function?

:

Tricks for what? Do you want to show something on your main
report
if
there
are no records in the subreport? If so, try the IIf() function
with
a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in
the
footer.
My
master report shows blank when the value is zero? Know any
tricks?
Thanks.

Barry

:

Comes up where? Do you have a control source that you would
like
to
share?
The normal method is to ignore any posts that suggest using
Nz()
since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text
box
express
0.
The
"#error" comes up when no records are found.

 

[BarryC]

Got it. Thanks Duane.

You messed up my expression by putting the cart in front of the horse
(report in front of subreport control name). Try:

=IIf([steve current month1].[Report].HasData,[steve current
month1].Report![countOfControllers],0)

--
Duane Hookom
MS Access MVP
--

=IIf([Reports]![steve current month1].HasData,[Reports]![steve current
month1]![countOfControllers],0)

This is the control source for the text box on the main report. The sub
report is named "steve current month1" and the text box on the sub report
is
named "countofcontrollers".

Could you provide the exact expression you use as the control source? Did
you change the names in the expression to match your actual names?

--
Duane Hookom
MS Access MVP
--

Yes, the main report is the problem. I have tried inserting a text box
with
the controll source IIF(subRpt.HasData...) All I get is the #NAME?
error
message.

:

The IIf() expression should be in the main report, not the subreport.
Isn't
the main report where you are having the problem?

--
Duane Hookom
MS Access MVP


Duane,

Sorry, still not getting it. I have the IIF statement in my sub
report.
The
sub report works fine by itself. When I insert it into my main
report
it
does
not show when the value is null. When there is a value in the sub
report
it
works fine.

:

In the control source where you want to display a total from the
subreport
or a 0. It would be something like:
=IIf(sbrptA.Report.HasData,sbrptA.Report.txtSum,0)

--
Duane Hookom
MS Access MVP
--

Yes, that is what I want to do. Where on the main report would I
include
the
IIF() function?

:

Tricks for what? Do you want to show something on your main
report
if
there
are no records in the subreport? If so, try the IIf() function
with
a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in
the
footer.
My
master report shows blank when the value is zero? Know any
tricks?
Thanks.

Barry

:

Comes up where? Do you have a control source that you would
like
to
share?
The normal method is to ignore any posts that suggest using
Nz()
since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text
box
express
0.
The
"#error" comes up when no records are found.

 

[Johnny Bright]

Duane, you rock! I had the same problem and thanks to your posting, I no
longer have a problem! I'm sure glad I did a search of past postings!
--
www.brightfuture.ca/bright
My email address can be found on my site.

In the control source where you want to display a total from the subreport
or a 0. It would be something like:
=IIf(sbrptA.Report.HasData,sbrptA.Report.txtSum,0)

--
Duane Hookom
MS Access MVP
--

Yes, that is what I want to do. Where on the main report would I include
the
IIF() function?

Tricks for what? Do you want to show something on your main report if
there
are no records in the subreport? If so, try the IIf() function with a
reference to the HasData of the subreport.

--
Duane Hookom
MS Access MVP
--

Thanks Duane,

=IIF(hasdata,count(*),0) is working. It is on a sub report in the
footer.
My
master report shows blank when the value is zero? Know any tricks?
Thanks.

Barry

:

Comes up where? Do you have a control source that you would like to
share?
The normal method is to ignore any posts that suggest using Nz() since
they
won't work. Then set your control source to something like:

=IIf([HasData]=True, Sum(Qty), 0)

--
Duane Hookom
MS Access MVP
--

If a sql statment returns no records how do I have a text box
express
0.
The
"#error" comes up when no records are found.