Hide graph if there is no data (1 Viewer)

[Poco_90]

Hi,
I have a report that contains 5 graphs, one for each day of the week. I run this report daily. When I run the report on a Monday, only the Monday graph displays data and the other graphs are blank as there is no data for that day just yet. Is it possible to hide these graphs if there is no data? I did find one suggestion to create a text box with the following code

 Private Sub Report()
    If [SumOfWed] = 0 Then
        Me.Graph24.Visible = False
    Else
        Me.Graph24.Visible = True
    End If

This does't work for me.

Graph24 is the name of Wednesdays graph and the row source for the graph is TRANSFORM Sum([Wed]) AS [SumOfWed] SELECT [Machine] FROM [qryShiftDays] GROUP BY [Machine] PIVOT [Shift];



Thanks in advance,
Poco

 

[JHB]

You could use the row source for each graph in combination with a recordset to determine if a graph has any data or not.
You should rename your graphs to a better name, like GraphManday, GraphTuesday etc.

 

[Poco_90]

JHB, thank you for your reply. I haven't used record-sets. I just Googled record-sets there and I wouldn't know where to start. Is there any other way to hide the graphs?

 

[JHB]

Is it possible for you to post (a stripped version) of database, with some sample data, + info which report has the graph?

 

[Poco_90]

Here is a stripped down version. I'd appreciate any feedback you have.

 

[JHB]

Try it now!

 

[Poco_90]

JHB, thank you for getting back to me and thanks for making the change I asked for. One question, when I print the report on a monday, the second page is completely blank. It is probably because of the way I have 5 graphs in the report design. Is there any way around this?

 

[JHB]

..the second page is completely blank. It is probably because of the way I have 5 graphs in the report design. Is there any way around this?

Yes it is, and no I don't think so, not in the way it is setup just now.
I you print it using the printer dialogbox, then you can choose Page 1 only.

 

[Poco_90]

Unfortunately that isn't an option. This report and several others in the same format will be automatically printed to PDF and displayed on screens each day. I'll try and format the report differently. Thanks for all your help.

 

[vbaInet]

See attached.

 

[Poco_90]

vbaInet thank you for your help. It is just what I needed.

 

[vbaInet]

No problemo!