Calc when zero value

  • Thread starter Thread starter SamDev
  • Start date Start date
S

SamDev

I have inserted a unbound text box in a group footer of a report. The
formula is to pull up a value from a subreport. It works fine except when
the value is zero or blank, I get the #Error.

I tried including an IIF and it makes no difference.

I'm sure it's something simple that I'm just not thinking of....

Thx
 
If it is possible that the subreport returns no records then you need to use
something like:
=IIf(subRptName!Report.HasData, subRptName.Report.txtTotal, 0)
 
I have tried:
=IIf(subRptName!Report.HasData, subRptName.Report.txtTotal, 0)

that and I got a #Name error

I want to the value of a field returned that is in the footer of the
subreport....so instead I tried:

=IIF(subRptName!Report.HasData,subRptName.Report!FieldName, 0) - this
doesn't work - I'm new to VB so I'm sure I'm missing something simple - at
least I hope so....

Much thanks...
 
Is the subreport in the same section of the report as your text box?
Make sure the name of the text box on the main report is not also the name
of a field in the report's record source.
 
The subreport is not in the same section as the text box - I have checked
the names and they are not the same.

What I have right now is:

=iif([SubreportName.Report!HasData,SubreportName.Report.fieldname,0)

Works fine if the value is not zero, if it's zero I get the error message.

?????

Thx,
 
=IIf(IsError([Subreport].[Report].[TxtTotal].[Value]),Null,[Subreport].[Report].[TxtTotal].[Value])

I have tried the above and I no longer get an error but if the customer has
more than one invoice to add it only includes the last invoice. The
subreport containing the data is a group footer (named Invoice Footer) of a
main report and the text box box above is in another group footer (Customer
Footer) of the main report.

Thx...

SamDev said:
The subreport is not in the same section as the text box - I have checked
the names and they are not the same.

What I have right now is:

=iif([SubreportName.Report!HasData,SubreportName.Report.fieldname,0)

Works fine if the value is not zero, if it's zero I get the error message.

?????

Thx,
Click to expand...
 
My suggestion had a dot before HasData
=iif([SubreportName.Report.HasData, SubreportName.Report.fieldname,0)

--
Duane Hookom
MS Access MVP


SamDev said:
The subreport is not in the same section as the text box - I have checked
the names and they are not the same.

What I have right now is:

=iif([SubreportName.Report!HasData,SubreportName.Report.fieldname,0)

Works fine if the value is not zero, if it's zero I get the error message.

?????

Thx,
Click to expand...
 
Thanks - that made a difference except, if the customer has
more than one invoice to add it only includes the last invoice in the total.
The
subreport containing the data is a group footer (named Invoice Footer) of a
main report and the text box box above is in another group footer (Customer
Footer) of the main report.



Duane Hookom said:
My suggestion had a dot before HasData
=iif([SubreportName.Report.HasData, SubreportName.Report.fieldname,0)

--
Duane Hookom
MS Access MVP


SamDev said:
The subreport is not in the same section as the text box - I have checked
the names and they are not the same.

What I have right now is:

=iif([SubreportName.Report!HasData,SubreportName.Report.fieldname,0)

Works fine if the value is not zero, if it's zero I get the error
message.

?????

Thx,


Duane Hookom said:
Is the subreport in the same section of the report as your text box?
Make sure the name of the text box on the main report is not also the
name of a field in the report's record source.

--
Duane Hookom
MS Access MVP


I have tried:
=IIf(subRptName!Report.HasData, subRptName.Report.txtTotal, 0)

that and I got a #Name error

I want to the value of a field returned that is in the footer of the
subreport....so instead I tried:

=IIF(subRptName!Report.HasData,subRptName.Report!FieldName, 0) - this
doesn't work - I'm new to VB so I'm sure I'm missing something simple -
at least I hope so....

Much thanks...


If it is possible that the subreport returns no records then you need
to use something like:
=IIf(subRptName!Report.HasData, subRptName.Report.txtTotal, 0)


--
Duane Hookom
MS Access MVP


I have inserted a unbound text box in a group footer of a report. The
formula is to pull up a value from a subreport. It works fine except
when the value is zero or blank, I get the #Error.

I tried including an IIF and it makes no difference.

I'm sure it's something simple that I'm just not thinking of....

Thx
Click to expand...
Click to expand...
Click to expand...
 
If each invoice is a subreport then you may need to put the text box in the
same section as the subreport and then set it up as a running sum.

--
Duane Hookom
MS Access MVP


SamDev said:
Thanks - that made a difference except, if the customer has
more than one invoice to add it only includes the last invoice in the
total. The
subreport containing the data is a group footer (named Invoice Footer) of
a
main report and the text box box above is in another group footer
(Customer
Footer) of the main report.



Duane Hookom said:
My suggestion had a dot before HasData
=iif([SubreportName.Report.HasData, SubreportName.Report.fieldname,0)

--
Duane Hookom
MS Access MVP


SamDev said:
The subreport is not in the same section as the text box - I have
checked the names and they are not the same.

What I have right now is:

=iif([SubreportName.Report!HasData,SubreportName.Report.fieldname,0)

Works fine if the value is not zero, if it's zero I get the error
message.

?????

Thx,


Is the subreport in the same section of the report as your text box?
Make sure the name of the text box on the main report is not also the
name of a field in the report's record source.

--
Duane Hookom
MS Access MVP


I have tried:
=IIf(subRptName!Report.HasData, subRptName.Report.txtTotal, 0)

that and I got a #Name error

I want to the value of a field returned that is in the footer of the
subreport....so instead I tried:

=IIF(subRptName!Report.HasData,subRptName.Report!FieldName, 0) - this
doesn't work - I'm new to VB so I'm sure I'm missing something
simple - at least I hope so....

Much thanks...


If it is possible that the subreport returns no records then you need
to use something like:
=IIf(subRptName!Report.HasData, subRptName.Report.txtTotal, 0)


--
Duane Hookom
MS Access MVP


I have inserted a unbound text box in a group footer of a report. The
formula is to pull up a value from a subreport. It works fine except
when the value is zero or blank, I get the #Error.

I tried including an IIF and it makes no difference.

I'm sure it's something simple that I'm just not thinking of....

Thx
Click to expand...
Click to expand...
Click to expand...
 
Ok - thanks for all your help & patience!!!


Duane Hookom said:
If each invoice is a subreport then you may need to put the text box in
the same section as the subreport and then set it up as a running sum.

--
Duane Hookom
MS Access MVP


SamDev said:
Thanks - that made a difference except, if the customer has
more than one invoice to add it only includes the last invoice in the
total. The
subreport containing the data is a group footer (named Invoice Footer) of
a
main report and the text box box above is in another group footer
(Customer
Footer) of the main report.



Duane Hookom said:
My suggestion had a dot before HasData
=iif([SubreportName.Report.HasData, SubreportName.Report.fieldname,0)

--
Duane Hookom
MS Access MVP


The subreport is not in the same section as the text box - I have
checked the names and they are not the same.

What I have right now is:

=iif([SubreportName.Report!HasData,SubreportName.Report.fieldname,0)

Works fine if the value is not zero, if it's zero I get the error
message.

?????

Thx,


Is the subreport in the same section of the report as your text box?
Make sure the name of the text box on the main report is not also the
name of a field in the report's record source.

--
Duane Hookom
MS Access MVP


I have tried:
=IIf(subRptName!Report.HasData, subRptName.Report.txtTotal, 0)

that and I got a #Name error

I want to the value of a field returned that is in the footer of the
subreport....so instead I tried:

=IIF(subRptName!Report.HasData,subRptName.Report!FieldName, 0) - this
doesn't work - I'm new to VB so I'm sure I'm missing something
simple - at least I hope so....

Much thanks...


If it is possible that the subreport returns no records then you
need to use something like:
=IIf(subRptName!Report.HasData, subRptName.Report.txtTotal, 0)


--
Duane Hookom
MS Access MVP


I have inserted a unbound text box in a group footer of a report.
The formula is to pull up a value from a subreport. It works fine
except when the value is zero or blank, I get the #Error.

I tried including an IIF and it makes no difference.

I'm sure it's something simple that I'm just not thinking of....

Thx
Click to expand...
Click to expand...
Click to expand...
 
Back
Top