Display column aliases from a query

[Grant Palin]

I'm creating a query for an Access database; the columns used in the query are stored in a dictionary object, with the column name being the key and the label being the value. By label I mean alias, where you would do "SELECT column AS col...". The problem I am running into is that labels may contain multiple words, although I know that column aliases must be one word or contained in quotes.

I generate a query based on the dictionary object as follows:

objStringBuilder.Add("SELECT ")

For Each strName In dicColumns.Keys
objStringBuilder.Add(strName & " AS " & dicColumns.Item(strName))
If intCount < dicColumns.Count - 1 Then
objStringBuilder.Add(", ")
End If

intCount = intCount + 1
Next

This code is in a function that uses the column name/label dictionary to generate a query. The function returns the complete query string.

So I get the following query:

SELECT LT1 AS Less Than 1, A1_4 AS 1-4...FROM data


This of course does not work: the query chokes on the second word in the first alias.

So, I try enclosing the label in quotes. The amended query generation code follows:

objStringBuilder.Add("SELECT ")

For Each strName In dicColumns.Keys
objStringBuilder.Add(strName & " AS """ & dicColumns.Item(strName) & """")
If intCount < dicColumns.Count - 1 Then
objStringBuilder.Add(", ")
End If

intCount = intCount + 1
Next


The query I get now is:

SELECT LT1 AS "Less Than 1", A1_4 AS "1-4"...FROM data


The query works. But because I don't know the column names or labels beforehand, I want to display the column aliases selected in the query. I use the following code to display each label as a table header:

For Each objField In objRS.Fields
objStringBuilder.Add(" <th>" & objField.name & "</th>" & vbCrLf)
Next

This works, but it also displays the quotes I used to indicate the column aliases! It's the same for both single and double quotes.

Are there any other things I could try? Without having to display those quotes in the table headers?

[gwendaal]

As [....]

[Grant Palin]

Thank you, that did the trick! I didn't know you could use square brackets!