Hi Allison,
Nice question! It is doable, but complicated because you have to work
around the issue. Here is one way, that works for a two-column report. It
involves a table, several queries and two versions of the same report. In
the following, I am using a report that contains an application year, ID,
last name and first name. It groups on the application year and sorts on the
last name and then the first name.
Set up your original report in this fashion: Make it so that it does
use the across and then down ordering. For your group header, make sure its
New Row or Col property is set to Before & After. In the detail section, add
an unbound text box. Set its Control Source to "=1", without the quotes.
Give it a name such as "txtItemNumber". Set its Running Sum property to Over
Group. Save.
Create a table. Add to it the fields needed to uniquely identify each
detail row in the report. Add two other rows, one for a collection number
and one for an item number. Collections will be determined by each group
break AND by each page break. For example, if it takes an application year
and an ID to identify an applicant, your table might look like this:
tblTwo_Column_Report_Ordering
APPLICATION_YEAR
ID
COLLECTION_NUMBER
ITEM_NUMBER
This table will be used to store information about where stuff ends up
in your report, as it currently works.
Create a query that uses the new table and for each report group
calculates the halfway item number. So for my example (named "qryTwo Column
Report Part 2"):
SELECT A.COLLECTION_NUMBER, Int((Max([ITEM_NUMBER])+1)/2) AS
HALFWAY_ITEM_NUMBER
FROM tblTwo_Column_Report_Ordering AS A
GROUP BY A.COLLECTION_NUMBER;
Create another query that uses the original report's query (here
"qryTwo Column Report Part 1"), the table and the new query in order to
determine a new order for the data. Named "qryTwo Column Report Part 3":
SELECT A.*, C.COLLECTION_NUMBER,
[ITEM_NUMBER]-IIf([ITEM_NUMBER]<=[HALFWAY_ITEM_NUMBER],0,[HALFWAY_ITEM_NUMBER]) AS ITEM_SORT
FROM ([qryTwo Column Report Part 1] AS A INNER JOIN
tblTwo_Column_Report_Ordering AS B ON (A.APPLICATION_YEAR =
B.APPLICATION_YEAR) AND (A.ID = B.ID)) INNER JOIN [qryTwo Column Report Part
2] AS C ON B.COLLECTION_NUMBER = C.COLLECTION_NUMBER;
Back in your original report add code for each of these events: Group
Header - On Format, Detail - On Format, Page Header - On Format, Report -
Close, Report - Open, Report Header - On Format. This code will compute and
save the collection and item numbers for each detail item in your report.
Here is the code from my example:
==============================================
Option Compare Database
Option Explicit
Private Const m_cstrTableName As String = "tblTwo_Column_Report_Ordering"
Private m_rstReportOrdering As ADODB.Recordset
Private m_lngCollectionNumber As Long
Private m_lngItemNumberOffset As Long
Private Sub APPLICATION_YEAR_GroupHeader_Format(Cancel As Integer,
FormatCount As Integer)
' Each application year group will start a new collection
m_lngCollectionNumber = m_lngCollectionNumber + 1
m_lngItemNumberOffset = 0
End Sub
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngCollectionItemNumber As Long
' Compute the item number within the current collection
lngCollectionItemNumber = [txtItemNumber].Value - m_lngItemNumberOffset
With m_rstReportOrdering
' Determine if the current item has a row in the report ordering table
.Open "select * from [" & m_cstrTableName & "] " & _
"where [APPLICATION_YEAR] = " & [txtAPPLICATION_YEAR].Value & "
and [ID] = " & _
[txtID].Value, _
CurrentProject.Connection, ADODB.CursorTypeEnum.adOpenDynamic, _
ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText
If .BOF And .EOF Then
' Current item not in table; add a new row
.AddNew
![APPLICATION_YEAR].Value = [txtAPPLICATION_YEAR].Value
![ID].Value = [txtID].Value
![COLLECTION_NUMBER].Value = m_lngCollectionNumber
![ITEM_NUMBER].Value = lngCollectionItemNumber
.Update
Else
' There is already a row for the current item
If ![COLLECTION_NUMBER].Value <> m_lngCollectionNumber Or _
![ITEM_NUMBER].Value <> lngCollectionItemNumber Then
' Occasionally, when formatting on succeeding passes, the
information is
' different
![COLLECTION_NUMBER].Value = m_lngCollectionNumber
![ITEM_NUMBER].Value = lngCollectionItemNumber
.Update
End If
End If
.Close
End With
' Showing the values on the report
[txtCollectionNumber].Value = m_lngCollectionNumber
[txtCollectionItemNumber].Value = lngCollectionItemNumber
End Sub
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As
Integer)
' Each page will start a new collection
m_lngCollectionNumber = m_lngCollectionNumber + 1
m_lngItemNumberOffset = [txtItemNumber].Value - 1
End Sub
Private Sub Report_Close()
' Clean up
On Error Resume Next
If m_rstReportOrdering.State <> ADODB.ObjectStateEnum.adStateClosed Then
m_rstReportOrdering.Close
End If
Set m_rstReportOrdering = Nothing
End Sub
Private Sub Report_Open(Cancel As Integer)
' Delete any existing records from the report ordering table
CurrentProject.Connection.Execute "delete from [" & m_cstrTableName &
"]", , _
ADODB.CommandTypeEnum.adCmdText
Set m_rstReportOrdering = New ADODB.Recordset
End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
' Start of report; starting the collection out at -1 as it will be
increased by one
' with the first application year group and by another one with the
first page
m_lngCollectionNumber = -1
End Sub
==============================================
Save your report. Copy and paste the report while in the database
window so you have a complete copy. Edit the copy and remove the item number
text box. In the report's properties, change the Record Source to the new
query that includes the collection number and the item sort. Also change the
Has Module property from Yes to No and when asked, tell it to go ahead and
delete the module. Next, change the grouping/sorting and insert the
collection number and item sort between your grouping and your other
sortings. So in my instance the grouping/sorting now looks like this:
APPLICATION_YEAR (group on)
COLLECTION_NUMBER
ITEM_SORT
LAST_NAME
FIRST_NAME
Note, that you leave the ordering of the report set to across and then
down. Close and save the copy of the report. To run this whole process you
need to open up the original report and go to the last page and then
optionally close it. This will have the effect of creating the necessary
rows in the report ordering table. Then when you open the copy of the
report, it should show with the data going down and then across in what you
call the newspaper column style. You can automate the process for your user
with code something like this, where "rptTwo Column Report Part 1" would be
your original report, as modified, and "rptTwo Column Report Part 2" would be
the further modified copy of the report:
==============================================
Public Sub OpenNewspaperColumnReport()
Const cstrReportPart1 As String = "rptTwo Column Report Part 1"
Const cstrReportPart2 As String = "rptTwo Column Report Part 2"
Const cstrTempFile As String = "C:\Temp\" & cstrReportPart1 & ".snp"
Dim fso As FileSystemObject
' Open the report that will determine and store the collection and item
numbers
DoCmd.OpenReport cstrReportPart1, acViewPreview, , , acHidden
' Need to go to the final page in order to make sure all values are
stored correctly;
' do this by outputting the report to a temporary file in the snapshot
format
Set fso = New FileSystemObject
If fso.FileExists(cstrTempFile) Then
fso.DeleteFile cstrTempFile
End If
DoCmd.OutputTo acOutputReport, cstrReportPart1, acFormatSNP, cstrTempFile
fso.DeleteFile cstrTempFile
DoCmd.Close acReport, cstrReportPart1, acSaveNo
' Now the actual report the user will view should work; open it
DoCmd.OpenReport cstrReportPart2, acViewPreview
Set fso = Nothing
End Sub
==============================================
Note that the newsgroup reader may mess with the line breaks in the
above codings so you will need to clean it up as needed.
Hope this helps,
Clifford Bass
Allison said:
Access 2003, Win XP
I've searched this section but can't seem to find this answer.
I have a report with no sub-report. The detail section (one line, set to
not grow) is grouped by division (a 5-digit number). I have the page setup
to 2-columns, down then across.
I cannot set this to across then down, because each division has variable
number of records which makes across-then-down columns confusing to read.
I then have a report footer that I want to print on the final page. HOWEVER
- setting columns to down then across leaves a giant white space in the final
column on the last page of detail, and forces the report footer to a new page.
If I set the detail to across then down, the report footer prints right
beneath the detail and makes the detail work like newspaper columns. This is
what I want the report to look like, but I need it to print down then across.
How can I force this behavior?
Allison
