change column name in query caption (or alias) in VBA

[holgar]

Hi,
Is there a way to change caption (or alias) property of a column in a query into f.ex. Me.Label1.caption?

 

[lupins46]

If the qury is a saved query then just put the alias in front of the actual fieldname.

Myalias:myoriginalfieldname

If it's a query in code then any column can be renamed:

"Select myoriginalfieldname as myalias from mytable"

 

[holgar]

I know how to manually change alias but this has to be done via VBA.

I forgot to mention that label caption text will be changing from month to month so I want column name in query to capture this label caption text.

 

[scriverb]

To modify a query in VBA code use the following as an example:

Dim db as DAO.Database
Set db = CurrentDB
db.QueryDefs("[i]yourqueryname[/i]").SQL = "Select A.Field1, A.Field2, A.Field3 as " & Me.Label1.caption & ", A.Field4 FROM [i]tablename[/i] as A ORDER BY A.Field1;"
db.close

This code will modify the actual SQL code of the query with VBA code to the caption value of the label on the form that is making the call to the sub. The query is an example an obviously you will want to adapt it to your query SQL code. Make sure to leave a space on either side of the label caption.

Post back if you have any questions.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]