Hide a particular record in report (1 Viewer)

wasim_sono · May 30, 2019

Dear All

I have a report based on crosstab query. I always insert dummy data as well as actual data in table and then run report. Dummy data has values "0". I want to hide this dummy data whenever I run the report.

Gasman · May 30, 2019

Base the report on a query that does not select the data with 0 values.?

wasim_sono · May 31, 2019

Thanks Gasman

But the problem is that I have to remain the data in report other wise report doesn't work. Is it possible that write some code on report format event to hide that particular record.

Gasman · May 31, 2019

I've not done much like that in reports, but you could try setting .visible = false in the detail line for all the controls?

The experts would be better to advise.

I've just had a quick play on one of my reports. This works.

Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Amount < 250 Then
    Debug.Print "Format " & Me.Amount
    Me.Amount.Visible = False
Else
    Me.Amount.Visible = True
End If
End Sub

HTH

isladogs · May 31, 2019

Do you want to just hide the field with the dummy 0 data or the entire record (all fields including that value?)

wasim_sono · May 31, 2019

I want to hide entire record.

isladogs · May 31, 2019

You could use conditional formatting so that the text colour is the same as the background colour (white?) for those fields.
You could all do the same in code by that method or using visible =false as gasman suggested. If there were multiple fields to hide, the tag property could be used to streamline the code required.

Either method would of course leave empty row where those records should be.

It would certainly be easier to filter out those records. How would that stop your report working?

Pat Hartman · Jun 2, 2019

The presence or absence of a record should not affect how a report works. What exactly does not work for the report if the "blank" record is simply not selected by the RecordSource query?

PS - NOBODY puts dummy data into tables. This sounds like a table that you are forcing to operate like a spreadsheet.

Micron · Jun 2, 2019

The presence or absence of a record should not affect how a report works.

It does when it's based on a cross tab as was stated. Fields in a cross tab disappear when there's not enough row based data to transpose. Suggest searching on

ms access "report" based on crosstab query
It's too involved an exercise to provide details here. Then again, it might not be the effect you're looking for.

isladogs · Jun 2, 2019

You can specify column headers for a crosstab report.
Doing so means you don't 'lose' values with no data.

Hide a particular record in report (1 Viewer)

wasim_sono

Registered User.

Gasman

Enthusiastic Amateur

wasim_sono

Registered User.

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

wasim_sono

Registered User.

isladogs

MVP / VIP

Pat Hartman

Super Moderator

Micron

AWF VIP

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread