INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Set Query Column Heading parameters through VBA for CrossTab query

Set Query Column Heading parameters through VBA for CrossTab query

(OP)
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.

RE: Set Query Column Heading parameters through VBA for CrossTab query

Please post your cross tab SQL.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Set Query Column Heading parameters through VBA for CrossTab query

(OP)
Here is the sql

CODE -->

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.

RE: Set Query Column Heading parameters through VBA for CrossTab query

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

RE: Set Query Column Heading parameters through VBA for CrossTab query

(OP)
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.

RE: Set Query Column Heading parameters through VBA for CrossTab query

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.

CODE --> 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 IN ("2015","2016","2017","2018","2019","2020"); 

Duane
Hook'D on Access
MS Access MVP

RE: Set Query Column Heading parameters through VBA for CrossTab query

(OP)
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

RE: Set Query Column Heading parameters through VBA for CrossTab query

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close