Change Column Headings property in Crosstab Query w/ VB

[mystuff]

Is it possible to change the Column Headings property in a Crosstab Query using VB?

Thanks.

 

[ZmrAbdulla]

TRANSFORM Sum(Issue.Amount) AS SumOfAmount
SELECT Issue.ItemName
FROM Issue
GROUP BY Issue.ItemName
PIVOT Format([IssueDate],"yyyy");

TRANSFORM Sum(Issue.Amount) AS SumOfAmount
SELECT Issue.ItemName
FROM Issue
GROUP BY Issue.ItemName
PIVOT Format([IssueDate],"yyyy") [b][COLOR=red]In (2003,2004,2005)[/color][/b];

First one is a crosstab query without defined column heading and second with defined as years 2003,2004 & 2005.
You can split this into VBA like below. You will be able to change it as you required.

Sub LoadList()
    Dim strSQL As String
    strSQL = "TRANSFORM Sum(Issue.Amount) AS SumOfAmount SELECT Issue.ItemName FROM Issue GROUP BY Issue.ItemName " & Me.TxtHeadings.Value
    Me.List0.RowSource = strSQL
End Sub
[COLOR=green]'=====================================[/color]
Private Sub cmdLoadList_Click()
    LoadList
End Sub
[COLOR=green]'===================================[/color]
Private Sub Form_Load()
    Me.TxtHeadings.Value = "PIVOT Format([IssueDate],'yyyy')In(2003,2004,2005,2006)"
End Sub

ComeBack if you need more help.

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[MichaelRed]

I have usually just added a source to the query with the "Column Headings" desired. You can mkae it an Outter Join to the desired Piviot Column and use it as the actual piviot column, thus creating the headings w/o any code.

You DO need to take care that the new source includes all of the desired (or possible) values (depending on the desired results), otherwise is is trivially possible to exclude (valid & meaningful) information - - - which would skew the results and possibly harm your standing.





MichaelRed