Zero to Null

B

Bruce Rodtnick

Can I reset a calculated field to null if the result of the calculation
is zero?

What I have is a report with four subreports in it. I have to add the
results of all the subreports and I've written code (with some of y'alls
help) that turns null values into zeros so the calculation can take
place. On most of the subreports, if there is no data in it, the report
shrinks to nothing.

But one of the subreports also has two subreports in it. It works fine
as long as there is data in at least one of those sub-subreports. But
if there is no data in both sub-subreports, the subreport above it still
calculates to a zero value and the results (0) shows on the report.
While I can make the subreport invisable if it has a zero value:

If Me.txtTravelTotal = 0 Then
Reports.Budget.subTravel.Visible = 0
End If

the space on the report is still taken up by the invisible subreport.
My thought is that I could make the result of the subreport null again
and the report would shrink and not take up any space in the report. Or
is there another way of doing this?

Bruce Rodtnick
 
M

Marshall Barton

Bruce said:
Can I reset a calculated field to null if the result of the calculation
is zero?

What I have is a report with four subreports in it. I have to add the
results of all the subreports and I've written code (with some of y'alls
help) that turns null values into zeros so the calculation can take
place. On most of the subreports, if there is no data in it, the report
shrinks to nothing.

But one of the subreports also has two subreports in it. It works fine
as long as there is data in at least one of those sub-subreports. But
if there is no data in both sub-subreports, the subreport above it still
calculates to a zero value and the results (0) shows on the report.
While I can make the subreport invisable if it has a zero value:

If Me.txtTravelTotal = 0 Then
Reports.Budget.subTravel.Visible = 0
End If

the space on the report is still taken up by the invisible subreport.
My thought is that I could make the result of the subreport null again
and the report would shrink and not take up any space in the report. Or
is there another way of doing this?
Click to expand...


The subsubreport control will shrink if you make it
invisible using code in the subreport detail(?)'s Format
event:
Me.subTravel.Visible = (Me.txtTravelTotal = 0)

Don't forget that both the subsubreport control and the
subreport section that contains it must have their CanShrink
property set to Yes.
 
D

Douglas J. Steele

Shouldn't that be Me.subTravel.Visible = (Me.txtTravelTotal <> 0), Marsh?
 
B

Bruce Rodtnick

Tried this, but it's giving me a "Method or data member not found" and
then it seems to lock up when I hit OK until I comment the line out.

By the way "txtTravelTotal" is in the report footer. It locks up if I
put that line in the Footer OnFormat also.

B
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SynNeed to account for null values in subreports 2
Access How to prevent subReport from breaking across pages? 1
Using Subreports to create a Main Report 7
Displaying blank space when no data in subreport 5
Report with pivot tables and pivot charts in sub reports 6
Excess vertical space between unbound subreports 3
Counting Null Values in Report 1
how many subreports? 4

Top