Report Visible Property.

[fermerius]

Hi!
I have a report, it contains fields Player and winnings. So each Player has a winning. And it looks like this:

Player_id - winnings
Jack - 50000
John - 25000
Ros - 45000
Joey - 31000

And I want to hide player_id field, for players whose winnings is less than 30000. I've tried to do it via macros by setting SetValue action with argument
[Reports]![Player1]![Player_ID].[Visible], 0
but it only works fine if you want to hide the whole Player_id column, but I need to hide just some of them. I tried to set a condition, but, obviously, it can't be met because it operates with the whole column of values... and thus condition Reports![Player1]![winnings]<30000 is incorrect.

Is there anyway I can do it ?

 

[pbaldy]

One way would be Conditional Formatting. You can't set Visible, but you can set the font color to achieve the same result.

 

[KDAnalyst]

You could also try to achieve this in a query using an IIF statement, then criteria for the findings (assuming the data source for your report is a query).

So, for example, create a calculated field within the query, based on [winnings]:
IIF([winnings]<30,000,"Hide","Visible"). In the criteria for this calculated field, use "Visible". This will work assuming you only have one row per player where the total winnings is recorded in a single row.

If the [winnings] field is a sum of multiple records for your Players, you could also create a crosstab query where Player_ID is your Row Source, "Total" is your column heading and Winnings is your Value (sum). Be sure to record your column headings in the crosstab query property to "Player_ID", "Total". Then, relate the crosstab query to your report source query (via the Player_ID field), add "Total" to it and choose >=30000 as the criteria (then, in your report, you would update your [winnings] field to [Total].

There may be an easier way to achieve this, but this is the way I've used for similar needs and it works as intended.

Hope that helps

 

[boblarson]

To make it invisible based on this, you need to use the ON FORMAT event of the section of the report that this control is in. So, if it is in the Detail Section then it would be in the On Format event of the Detail Section.

   Me.winnings.Visible = (Me.winnings > 30000 )

That one line should do the trick.

 

[KDAnalyst]

Thanks for teaching me the simple way of doing this! You seem to be the "Access guru" on this site, so maybe you would have an answer for me on a similar challenge I'm having.

Basically, I have two subreports within a main report. The linked one displays however number of related records are entered and is set to grow, but not shrink (it's current height makes it pretty much invisible so if it's empty, nothing will appear other than the label heading, which is what I need it to do).

Users wanted to have a number of blank rows appear if nothing is within the linked subreport (for post-printing manual recording of information, so this is the reason for the unbound 2nd subreport).

Basically, I'd like the unbound subreport to be visible ONLY when the linked subreport contains no records. I've attempted this using the following code (OnPrint of the detail section):

'Makes visible the manual CAPA sheet if no CAPAs entered, or displays entered CAPAs
If IsNull(Me.sbrCAPAs) Then
Me.sbrCAPAs.Visible = False
Else: Me.sbrBLANK_CAPAs.Visible = True
End If

It doesn't seem to be working. Any suggestions?

Thanks in advance,
Kathleen.

 

[boblarson]

I believe you would need to put it on the On FORMAT event as well and not in the On Print event.

 

[boblarson]

You seem to be the "Access guru" on this site,

And no, I just answer a lot of questions. Paul (pbaldy) and others are just as "guru" as me, if not more. I'm just possibly more visible due to the number of posts I answer.

 

[KDAnalyst]

Hmmm....tried that and it still displays both.

I wonder if I set the IsNull criteria to a particular control on the linked subreport rather than the entire subreport? What do you think (I'll give it a try)? Or, do you see anything wrong with the code (just tweaked it again, and it now hides the unbound subreport regardless if the linked one contains no data):

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Makes visible the manual CAPA sheet if no CAPAs entered, or displays entered CAPAs
If IsNull(Me.sbrCAPAs) Then
Me.sbrCAPAs.Visible = False
Me.sbrBLANK_CAPAs.Visible = True
Else: Me.sbrBLANK_CAPAs.Visible = False
Me.sbrCAPAs.Visible = True
End If
End Sub

 

[gemma-the-husky]

you may have more chance testing the record count of the subreport query

something like this

subreport1.visible = dcount("*",subreport2.recordsource)=0

 

[KDAnalyst]

Thank you David.

Where would this be done (within the properties of the query, the subreport, the main report...on a particular event)? Sorry for my ignorance

Also, I'm not sure what this means (would like to understand what exactly this will do)?

Is this similar to an IIF statement where "subreport1.visible" is the command to determine if it should be visible, and what follows is the criteria (where if no records are returned for the linked subreport, the unbound subreport is visible)?

Thanks again,
Kathleen.