Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crosstab type question

Status
Not open for further replies.
Joined
May 9, 2000
Messages
446
Location
GB
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
 
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.
 
cheers mustangirl, will give it a go...
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top