Set Query Column Heading parameters through VBA for CrossTab query

[mmogul]

Hi

I am trying to set the Column Headings parameters of a crosstab query via VBA. I cannot seem to find the syntax to do it. I thought it would be something like:



CurrentDB.QueryDefs("qryName").Properties("Column Names") = sColumnText

But I don't think "Column Names" is the correct way to reference the Column Names property.

 

[SkipVought]

Please post your cross tab SQL.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mmogul]

Here is the sql

TRANSFORM Sum(tblTemp_Rpt_Consolidate.ConvertedAmount_a) AS SumOfConvertedAmount_a
SELECT tblTemp_Rpt_Consolidate.Account, tblTemp_Rpt_Consolidate.AccountName AS [Account Name]
FROM tblTemp_Rpt_Consolidate
WHERE (((tblTemp_Rpt_Consolidate.Region)=gettblzadmin("Reports_CurrentRegion")) AND ((tblTemp_Rpt_Consolidate.GAAPType)=gettblzadmin("Reports_CurrentGAAPType")))
GROUP BY tblTemp_Rpt_Consolidate.Account, tblTemp_Rpt_Consolidate.AccountName
ORDER BY tblTemp_Rpt_Consolidate.Account
PIVOT tblTemp_Rpt_Consolidate.PeriodRpt;

The Where clause is selecting a region and a GAAP Type

The reason I am trying to set the column names is because this query is inserted into the same Excel Sheet - multiple times (example: First Run - values might be in rows 1-10, then the second pass is run for the next GAAP type and the data will be place in Rows 12-21 etc).

The problem is that the different version may not all use the same time periods, so the data of one version will not line up with the data of another version.

What I was trying to do was:
Run the query without the where clause, retrieve all the column names, and place them in a string variable. Then I wanted to programmatically change the Column Headings property by assigning the string variable.

Thanks for your help.

 

[dhookom]

If I understand, you are attempting to alias the PeriodRpt values. If this is the case I would consider creating a small table with a primary key of the PeriodRpt values and another field to use as the Column Headings. This would allow you to assign any PeriodRpt to any Column Heading. Just join the small table to tblTemp_Rpt_Consolidate table using the PeriodRpt columns.

Duane
Hook'D on Access
MS Access MVP

 

[mmogul]

Thanks Duane. But if I understand you correctly ... I am not trying to alias the PeriodRpt values but ensure that all required values exist. For example, assume the entire tblTemp_Rpt_Consolidate contained periods: 2015-2020

This particular selection (using the Where clause) only contains 2016-2019. I still want the cross tab to display years 2015-2020.

Your reply made me realize that I could append a record with a zero value for each year for one key value to force the crosstab. I think that is a viable approach but I would like something a bit more elegant if possible.

 

[dhookom]

Do you realize you can use the Column Headings property to set all column headings even if there is no data? If you have a dynamic column list, you can use some DAO code to change the SQL property of your saved crosstab query.

TRANSFORM Sum(tblTemp_Rpt_Consolidate.ConvertedAmount_a) AS SumOfConvertedAmount_a
SELECT tblTemp_Rpt_Consolidate.Account, tblTemp_Rpt_Consolidate.AccountName AS [Account Name]
FROM tblTemp_Rpt_Consolidate
WHERE (((tblTemp_Rpt_Consolidate.Region)=gettblzadmin("Reports_CurrentRegion")) AND 
((tblTemp_Rpt_Consolidate.GAAPType)=gettblzadmin("Reports_CurrentGAAPType")))
GROUP BY tblTemp_Rpt_Consolidate.Account, tblTemp_Rpt_Consolidate.AccountName
ORDER BY tblTemp_Rpt_Consolidate.Account
PIVOT tblTemp_Rpt_Consolidate.PeriodRpt [highlight #FCE94F]IN ("2015","2016","2017","2018","2019","2020")[/highlight];

Duane
Hook'D on Access
MS Access MVP

 

[mmogul]

Duane -

I was trying to change the columns heading property. What I did not realize is that you do it through the SQL code. That looks like the solution to me.

Thanks so much for your help.

Mark

 

[SkipVought]

Mark, don't forget to enhance your thanks to Duane by hitting Great Post!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]