I have the following SQL code:

SELECT tblCustomers.customerID AS ID
FROM tblCustomers
WHERE (((tblCustomers.customerID)=1));


The select query is the row source of a listbox. How do I get the ID alias to appear in the column head? The query is going to be created using Querydef so it's not fixed otherwise I could have made the Caption property equal to ID.

In COLUMN HEADS properties put YES.

Yes it is.

a slightly confusing question because you said:
The query is going to be created using Querydef so it's not fixed
a querydef is fixed. querydefs are the objects found in the database window under 'Queries'.

i think you're saying you're doing this "on the fly," so one option would be to add a caption to the table. the table-caption will show up in the column head and you won't need the alias.

Sorry, I meant on the fly. The problem is that I want the caption in the table to remain as Customer ID but only in the list box I want it to say ID to reduce column width as I have a lot of columns. I want to the same with some other columns as well.

you can create a querydef, set the caption property, then delete the querydef:

Dim sql As String
Dim qdf As QueryDef

sql = "SELECT tblCountry.CountryID, tblCountry.Country" & _
" FROM tblCountry;"

Set qdf = CurrentDb.CreateQueryDef("qryCountryNames", sql)

With qdf
qdf.Fields(0).Properties("Caption") = "ID"
End With

With Me.lstbox
.ColumnHeads = True
.ColumnCount = 2
.ColumnWidths = ".5cm;3cm;"
.RowSource = qdf.Name
End With

CurrentDb.QueryDefs.Delete "qryCountryNames"

Thanks wazz. I'll look into that and get back to you if I have any problems.

How would you do the same exercise if the query column in design was an amalgamation ot 2 fields?

sql = "SELECT tblCountry.CountryID & "" / "" & tblCountry.Country AS Cntry" & _
" FROM tblCountry;"
i found that:
- aliases don't work on the autonumber field;
- aliases work if the autonumber field is concatenated with another field;
- aliases work on non PK fields.

one thing i forgot to do on the first try is adjust the quotes in the middle of the sql string (see above).

Thanks wazz.

I've noticed that you can only change the field caption in a query IF that field in the table has a caption in the first place. Is there a way to overcome this?

Private Sub cmdGetCountries_Click()

'Code written by wazz 2008-12-02
'Last modified by wazz 2008-12-07
'Source: http://www.access-programmers.co.uk/forums/showthread.php?p=783555#post783555

On Error GoTo ErrorHandler

Const conPropertyNotFound = 3270

Dim db As DAO.Database
Dim sql As String
Dim qdf As QueryDef
Dim prp As Property

Set db = CurrentDb
sql = "SELECT tblCountry.CountryID, tblCountry.Country FROM tblCountry;"

'Find out if a query already exists by the name you want to use.
'consider your options...
For Each qdf In db.QueryDefs
If qdf.Name = "qryCountryNames" Then
db.QueryDefs.Delete qdf.Name
Exit For
End If
Next qdf

'Create the querydef.
Set qdf = db.CreateQueryDef("qryCountryNames", sql)

'Add a caption to the first column. If the property does not exist
'it will be created in the error handler.
With qdf
qdf.Fields(0).Properties("Caption") = "ID"
End With

'Set listbox properties.
With Me.lstCountries
.ColumnHeads = True
.ColumnCount = 2
.ColumnWidths = ".5cm;3cm;"
.RowSource = qdf.Name
End With

'Delete the query. Set the row source of the form to "" on close
'or you will get an error when the form is reopened that the
'query does not exist.
db.QueryDefs.Delete "qryCountryNames"

ProcExit:
Set db = Nothing
Set qdf = Nothing
Set prp = Nothing
Exit Sub
ErrorHandler:
' Create the qdf property if it isn't already there.
If Err = conPropertyNotFound And CurrentDb.Updatable Then
Set prp = qdf.Fields(0).CreateProperty("Caption", dbText, "ID")
qdf.Fields(0).Properties.Append prp
Err = 0
Resume Next
Else
Your Error Handler
Resume ProcExit
End If

End Sub

Thanks wazz.