Crosstab type question

[vanillapod]

I have a crosstab based sub-report that reports on values in each yearly quarter. However at the beggining of the year there's no data for quarters 2,3 or 4 so when the report is opened it complains about not finding [2] ect and closes. Is there a way that i can tell the report to skip columns in the crosstab if there is no data, i don't want to have to make a dynamic crosstab cuz its a pain....

Cheers

 

[mustangirl]

IN YOUR CROSSTAB QUERY, SELECT PROPERTIES, THEN UNDER COLUMN HEADINGS PUT EXAMPLE: "1","2","3","4", OR HOWEVER THE FIELD NAME VALUE FOR THE COLUMN HEADING WOULD APPEAR IF ALL WERE THERE. THAT AWAY IF ONLY VALUE 1 WAS THERE DURING THE FIRST QUARTER, IT WOULD POPULATE COLUMN 1 AND IT WOULD PRODUCE COLUMNS 2-4, SO THE REPORT WOULD ALWAYS MATCH. HOPE THIS HELPS! IF NOT LET ME KNOW.

 

[vanillapod]

cheers mustangirl, will give it a go...

 

[mustangirl]

i got mine to work with the help of a friend; try this!

Private Sub Report_Open(Cancel As Integer)
' You didn't know how many columns, or what
' their names would be, until now.
' Fill in the label captions,
' and control ControlSources.

Dim intColCount As Integer
Dim intControlCount As Integer
Dim I As Integer
Dim strName As String, strSQL As String

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

On Error Resume Next

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

strSQL = "Select * from [" & Me.RecordSource & "]"

rst.Open Source:=strSQL, ActiveConnection:=cnn, LockType:=adLockReadOnly

intColCount = rst.Fields.Count
intControlCount = Me.Detail.Controls.Count


If intControlCount < intColCount Then
intColCount = intControlCount
End If


' Fill in information for the necessary controls.
For I = 1 To intColCount
strName = rst.Fields(I - 1).NAME
Me.Controls(&quot;lblHeader&quot; & I).Caption = strName
Me.Controls(&quot;txtData&quot; & I).ControlSource = strName

Next I

' Hide the extra controls.
For I = intColCount + 1 To intControlCount
Me.Controls(&quot;txtData&quot; & I).Visible = False
Me.Controls(&quot;lblHeader&quot; & I).Visible = False
Me.Controls(&quot;txtSum&quot; & I).Visible = False
Next I

' Close the recordset.
rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing
End Sub

hope it works for you!
SHAWNDRA CREE JONES,
DATABASE DEVELOPER
TOYOTA MOTOR MANUFACTURING NORTH AMERICA
ERLANGER, KY

 

[mustangirl]

sorry i posted this to the wrong thread, but this will dynamically write a crosstab or any other query to a report. SHAWNDRA CREE JONES,
DATABASE DEVELOPER
TOYOTA MOTOR MANUFACTURING NORTH AMERICA
ERLANGER, KY