Separate page for each year

  • Thread starter Thread starter mo
  • Start date Start date
M

mo

I have set the recordsource for a report with some code (below) which picks
up parameters from a form.

Users have asked if they can have figures for different years to be printed
on seperate pages. At the moment the report simply prints out figures for
one year for the different hospitals and then moves on to the next year and
so on.

I'm not really sure how this can be done. Anyone like to shed some light?

Code:

If IsNull(Forms!frmRecruitNew.cmb_hospcode) Then
strWhere = ""
strAnd = "WHERE RecruitYear = '" & (Forms!frmRecruitNew.cmbRecruitYear)
& "'"
Else
strWhere = "WHERE HospCode = '" & (Forms!frmRecruitNew.cmb_hospcode) &
"' "
strAnd = "AND RecruitYear = '" & (Forms!frmRecruitNew.cmbRecruitYear) &
"'"
End If

If IsNull(Forms!frmRecruitNew.cmbRecruitYear) Then
strAnd = ""
strWhere = "WHERE HospCode = '" & (Forms!frmRecruitNew.cmb_hospcode) &
"'"
End If

If IsNull(Forms!frmRecruitNew.cmb_hospcode) And
IsNull(Forms!frmRecruitNew.cmbRecruitYear) Then
strWhere = ""
strAnd = ""
End If

strSQL = "TRANSFORM Count(TBL_REGISTRATION0003.AutoNum) AS CountOfAutoNum "
strSQL = strSQL & "SELECT Format([entrydate],'yyyy') AS Recruit_Year,
HospCode, Count(AutoNum) AS AnnualTotal, "
strSQL = strSQL & "Abs(Sum(DateDiff('d', Date(),[eddbylmp])<=0)) AS
NoOfDelivs "
strSQL = strSQL & "FROM qry_rec "
strSQL = strSQL & strWhere
strSQL = strSQL & strAnd
strSQL = strSQL & " GROUP BY Format([entrydate],'yyyy'), HospCode "
strSQL = strSQL & "ORDER BY Format([entrydate],'yyyy') "
strSQL = strSQL & "PIVOT Format([EntryDate],'mmm') IN
('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')"

Me.Report.RecordSource = strSQL
 
Use a group header and footer, with the year in the header
portion and anything or nothing in the year group footer.
Right click on the footer bar and choose new page after
section.

-----Original Message-----
I have set the recordsource for a report with some code (below) which picks
up parameters from a form.

Users have asked if they can have figures for different years to be printed
on seperate pages. At the moment the report simply prints out figures for
one year for the different hospitals and then moves on to the next year and
so on.

I'm not really sure how this can be done. Anyone like to shed some light?

Code:

If IsNull(Forms!frmRecruitNew.cmb_hospcode) Then
strWhere = ""
strAnd = "WHERE RecruitYear = '" & (Forms! frmRecruitNew.cmbRecruitYear)
& "'"
Else
strWhere = "WHERE HospCode = '" & (Forms! frmRecruitNew.cmb_hospcode) &
"' "
strAnd = "AND RecruitYear = '" & (Forms!
Click to expand...
frmRecruitNew.cmbRecruitYear) &
"'"
End If

If IsNull(Forms!frmRecruitNew.cmbRecruitYear) Then
strAnd = ""
strWhere = "WHERE HospCode = '" & (Forms! frmRecruitNew.cmb_hospcode) &
"'"
End If

If IsNull(Forms!frmRecruitNew.cmb_hospcode) And
IsNull(Forms!frmRecruitNew.cmbRecruitYear) Then
strWhere = ""
strAnd = ""
End If

strSQL = "TRANSFORM Count(TBL_REGISTRATION0003.AutoNum) AS CountOfAutoNum "
strSQL = strSQL & "SELECT Format([entrydate],'yyyy') AS Recruit_Year,
HospCode, Count(AutoNum) AS AnnualTotal, "
strSQL = strSQL & "Abs(Sum(DateDiff('d', Date(), [eddbylmp])<=0)) AS
NoOfDelivs "
strSQL = strSQL & "FROM qry_rec "
strSQL = strSQL & strWhere
strSQL = strSQL & strAnd
strSQL = strSQL & " GROUP BY Format([entrydate],'yyyy'), HospCode "
strSQL = strSQL & "ORDER BY Format([entrydate],'yyyy') "
strSQL = strSQL & "PIVOT Format([EntryDate],'mmm') IN
('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct
','Nov','Dec')"

Me.Report.RecordSource = strSQL


.
Click to expand...
 
Back
Top