exporting alias query column headings

[datacontrol]

I have an alias query set up, to get rid of generic field names. When I export the query to excel via Access or via VBA code, the alias field names are rest to the generic ones. Is there any way to retain the alias headings when exporting?

thanks.

 

[spacepro]

Have you tried selecting each field in the design grid then rightclick and select properties and enter your headings in the Caption property?

Andy

 

[Pat Hartman]

I didn't loose the alias'. Tell us exactly how you are doing the export and post the query.

 

[datacontrol]

I posted my sub routine and sql below. I used the caption property in my alias query to set headings.

SELECT test.Field1, test.Field2, test.Field3, test.Field4, test.Field5, test.Field6, test.Field7, test.Field8, test.Field9, test.Field10, test.Field11, test.Field12, test.Field13, test.Field14, test.Field15, test.Field16, test.Field17, test.Field18, test.Field19, test.Field20, test.Field21, test.Field22, test.Field23, test.Field24, test.Field25, test.Field26, test.Field27
FROM test;

_______________________________________________


Private Sub Command161_Click()
Dim strFilter As String
Dim strSaveFileName As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

'here you would probaly want your Transfertext code
'for example

DoCmd.TransferSpreadsheet acExport, 8, "test (dump) Without Matching after", strSaveFileName, True, ""

MsgBox "Your data has been saved", vbOKOnly
End Sub

 

[Pat Hartman]

The caption property is not the same as an alias. This query shows how you create an alias:
SELECT tblCompany.CompanyID, tblCompany.CompanyName AS TestCompName, tblCompany.CompanyLoc
FROM tblCompany;
When you create an alias this way, it is the exported column name.

The caption property is just used within the db as the value for labels when forms/reports are created and as the column name in queries when you open them in datasheet view. The caption property is NOT the column name when you open the query in code or use it as the recordsource for a form or report. For example if you entered CompName as the caption for Field2, CompName would appear as the caption value in the label attached to the Field2 control but the Field2 control would still have Field2 as its controlsource and name properties.

You have two choices.
1. Rename the columns in the table.
2. Create real alias names in your query.