Exporting query to csv file: changing field names

[Remster]

Hi Folks

I need to export a query to a csv file using Visual Basic, but in the csv file two of the fields need to have the same name. So, for example, the fields called "Location_1" and "Location_2" in the query need both to be called "Location" in the csv file. How can I use Visual Basic to change the field names on exporting?

NB Ideally both fields would be called "Location" in the query too, but trying to do this brings up a "duplicate output alias" error.

Thanks

Remster

[ItsMe]

You can use automation to edit the values in cells. Something like
xlApp.ActiveSheet.Cells(1, 5).Value = "Location"

https://www.accessforums.net/macros/...tml#post240852

[JoeM]

Access won't let you name two fields in the same query with the same name (hence, you would not be able to export them with the same name using normal exporting conventions).

I might approach this a little differently.

First, create a one record table that lists all the field name that you want. So in that table, your Field names might be something like "Field1, Field2, Field3, etc", and the values in your one record are the actual field names that you want to export. Then add an extra field at the end named "Sort1" and set this value to 1.

Then, in your original query that has the actual data that you want to export, add a "Sort1" field to the end, and set this value to 2.

Then, create a Union Query which combines your two queries together, and sorts by the "Sort1" field, so your "field names" show up in the first row.
Then exporting the data WITHOUT headers.

Note that if you have fields in your original query that are NOT text (i.e. numeric or date), you may need to use the FORMAT function to convert them to Text in order to combine them in your Union query (fields that line up usually have to be the same data type).

Other options include:
- To write VBA to export the records line-by-line (and you can tell it what to export for the first row, your field names)
- Export it as it is, using field names "Location" and "Location_1", and after export replace "Location_1" with "Location", either manually or by some sort of script.
- Create a one-line CSV file that has all the headers just the way you like them. Export the data without headers. Use a script to combine the one-line header CSV file with your data file.

[Remster]

Many thanks for your replies.

JoeM, I'm going with your main suggestion. Just one more puzzle. I want if possible to achieve everything in one query, but I don't want to export the Sort1 column. At the moment I have:

SELECT Stuff1, Stuff2, Stuff3, 2 As Sort1 ----- I can't add fields to this table
FROM Stuff
UNION ALL
SELECT Field1, Field2, Field3, Sort1
FROM Fields
ORDER BY Sort1, Stuff3

Any suggestions? (I can get the field names at the start by selecting the fields from Fields before I select the fields from Stuff, but then I can't sort by Stuff 3, because I can sort only by fields from the first table.)

[JoeM]

You could create a query like this (and give it any name, I will use "Query1" for this example.:

SELECT Stuff1, Stuff2, Stuff3, 2 As Sort1
FROM Stuff
UNION ALL
SELECT Field1, Field2, Field3, Sort1
FROM Fields;

You can then create a query off of this query.
Add all the, including the SORT1 field.
Then uncheck the "Show" box under the SORT1 field, but select the "Ascending" sort option under it.
Then it will include this field in your sort, but not your export if you export this second query.

[Remster]

Ah, I see, a second query that selects all the fields from Query1. That would certainly save me the trouble of listing them.