Solved

Dynamically Set Query "Column Headings" Property

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

770 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