Can I tell Access NOT to print a line or to move on to the next Group

[GoodyGoody]

Hi,

I am printing a report where I want to print UP TO the first 3 lines in each Group. In this case it is a report for runners by Age Category and I just want to show the top 3 (max) in each age category. I believe the Format event runs before the Print event so I was wondering if I could put some VBA code in there to check the condition (> 3 print lines) and then to just not print any more lines in that Group?

Thanks as usual in advance
Stephen

 

[June7]

Build a TOP N by group query. Review http://allenbrowne.com/subquery-01.html#TopN

 

[arnelgp]

if you don't have autonumber field (ID), you must add it now.

you can create a Query that will bring only 3 (or less) records.
then you can use this Query as the recordsource of your Report.

select firstname, lastname, [age category] from yourTable where
  (select count("1") from yourTable as T where 
     T.[age category] = yourTable.[age category] and T.ID <= yourTable.ID) < 4;

 

[GoodyGoody]

Thanks for the tips guys but I would like to use the same input query to run a report just grouped differently to try and keep the number of queries and reports to a minimum. And anyway, I was just wondering if it is logically possible in Access 2013 reports to decide, for whatever reason, not to print a line on the report.

 

[June7]

Not that I know of. It is possible to force last record of report to repeat but never seen anything to suppress a record other than filtering.

I understand wanting to reduce number of objects. I faced same goal. It is possible to modify report RecordSource during its Open event. Example:

Private Sub Report_Open(Cancel As Integer)
'report is called by SampleManagement and zPre2009SamplesView forms to print list of samples
'set RecordSource to pull data from appropriate tables
Me.RecordSource = Nz(Me.OpenArgs, Me.RecordSource)
End Sub

But how would report 'group differently' with same source query?

 

[MajP]

And anyway, I was just wondering if it is logically possible in Access 2013 reports to decide, for whatever reason, not to print a line on the report.

The following skips the row where carID = 3. Has to be done in print preview.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  If Me.carID = 3 Then
   Cancel = vbCancel
   Me.MoveLayout = False
  End If
End Sub