crosstab report colum headings

[javablood]

Hi,

I have a crosstab report based on Duane Hookom's crosstab database. It
works very well and I appreciate all the help I received last year in getting
it to work. Now, I have a lot of data to report and I am trying to condense
it down to as few pages as possible. I can get the data (detail) to be 10
columns wide in the report and still be readable. However, the last two
column headings wrap into the next row even though there is plenty (at least
it appears that way) of width room left on the page.

I have tried various things, but really do not know how to make it not wrap.
I am fairly sure that it has something to do with the subreport I use for the
colum headings but . . .

Please help a frustrated hacker who has no more hair to pull out!

 

[Duane Hookom]

If you are using the dynamic crosstab report solution, what value are you
using for pbytNumColumns?

Also check the design view of the crosstab query to see what is in the
Column Headings property. If this property is
Column Headings: "A","B","C","D","E","F","G","H"
you will see only 8 columns. To see 10 columns, add "I","J".

 

[javablood]

Duane,

Thanks for your response. I do have the column headings set through "J" but
I am unsure about the pbytNumColumns. That's what happens when I only do
this on an as needed basis. Anyway, here is my module for the pbyNumColumns
but I do not see where the 'value' is set!

Option Compare Database
Option Explicit

Function UpdateIDAlias(pbytNumColumns As Byte) As Long
'============================================================
' Purpose:
' Copyright: 1999 Business Results
' Company: Business Results
' Phone: 715-835-8130
' E-Mail: (e-mail address removed)
' Programmer: Duane Hookom
' Called From: frmIDreports
' Date: 1/22/00
' Parameters:
'============================================================
On Error GoTo UpdateIDAlias_Err
Dim strErrMsg As String 'For Error Handling

Dim strSQL As String
Dim intAlias As Integer
Dim lngID As String
Dim bytLevel As Byte
Dim bytMaxColumns As Byte

Dim db As DAO.Database
Dim rs As DAO.Recordset

bytMaxColumns = pbytNumColumns

Set db = CurrentDb
Set rs = db.OpenRecordset("tblIDAlias") 'table used to redefine/alias the
column headings

With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngID = !Report_Group
bytLevel = 0
intAlias = 65 'ascii value of 'A'
Do While !Report_Group = lngID
.Edit
!Level = bytLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With

UpdateIDAlias_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

UpdateIDAlias_Err:
Select Case Err
Case Else
UpdateIDAlias = Err.Number
Resume UpdateIDAlias_Exit
End Select

End Function

 

[Duane Hookom]

The number of columns generated will be limited to the value set in this line:
bytMaxColumns = pbytNumColumns
pbytNumColumns should be passed into the function as an argument.
Function UpdateIDAlias(pbytNumColumns As Byte) As Long

 

[javablood]

Duane,

Please excuse my shortage of knowledge on this but how do I pass it as an
argument? The report is opened from a form and I do not see how this is done.

 

[javablood]

Duane,

Okay I think I understand, maybe! In the form I use to set up the report I
have lboNumColumns from which the number of columns is selected. That is
where I select 10 but still get the column heading wrap. So I am passing a
max column as 10! Yes?

 

[Duane Hookom]

If you call the function with 10 as the argument then it should create a
crosstab with 10 columns. Did you look at the datasheet view of the report's
record source? How many fields are there?

 

[javablood]

Duane,

I am sorry but maybe I got you confused. Yes, I am getting 10 columns.
However, the heading columns are wrapping into the next row (actually 2
heading columns appear in the next row) whereas the data are in 10 columns
without wrapping. So how do I get the heading columns to display across the
width of the page as does the data?

 

[Duane Hookom]

Try set the srpt...headings Page Setup to Number of Columns: 10.
Make sure the width of this report allows this.

 

[javablood]

Duane,

That easy eh? Yes, that did the trick.

Now, how do I get the heading columns to repeat on each page?

 

[javablood]

repeat section = Yes. I found this in another one of yuor psots.

Thanks so much Duane.