Solved

Dynamically Set Query "Column Headings" Property

Posted on 2008-09-29
5
311 Views
Last Modified: 2012-06-27
I want to set a query's Column Headings property in VBA. Should be something like the below, just not sure of the syntax.
Any help is appreciated!
Thx,
MV
CurrentDb.QueryDefs (["qry_MYQUERY"].Properties.columnheadings = "A, B, C, D")

OR

Dim QDef As DAO.QueryDef

Set QDef = CurrentDb.QueryDefs("qry_MYQUERY")

QDef.Properties.columnheadings = "A, B, C, D")

Open in new window

0
Comment
Question by:Michael Vasilevsky
  • 3
  • 2
5 Comments
 
LVL 84
ID: 22599842
There is no such property. The only way to do this is to alias your columns:

SELECT sCustFirstName AS [First Name], sCustLastName AS [Last Name] FROM tCustomers

this would show the query with "First Name" and "Last Name" columns.
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 22600028
For a crosstab query there is. At least in design view. Is it not accessible through VBA? My problem is with a cross tab if there are no values in a crosstab column that column disappears and my formatting is messed up when I export to Excel...
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 22600905
Ah figured it out. I need to use "PIVOT tbl_MyTable.MyField In ('" & "A" & "', '" & "B" & "', '" & "C" & "');

That forces three columns regardless of if A, B, or C don't have any data.
Thanks!

MV
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22603523
Yes, it "aliases" query column names ... which is what I suggested you do. In other words, I provided you at least part of the answer.
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 22613061
Ok the points are yours ;-)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now