Solved

Dynamically Set Query "Column Headings" Property

Posted on 2008-09-29
5
327 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

756 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