-----Original Message-----
Here's one:
1) delete your special header record (but keep a backup copy of the
database<g>)
2) create a Select query that returns all the fields you want to export.
Insert a column at the beginning of the query, with the expression
SortOrder: 2
This will add a dummy field to the query results which we can later use
to distinguish the header from the data. Don't do anything else about
sorting at this stage. Save this query.
3) create another Select query based on the same table (or any other
table, it doesn't matter). Don't add any fields to the query, but switch
to SQL view. It will look like this:
SELECT
FROM MyTable;
Now the idea is to edit this so it returns one row of dummy data for the
header record. It will look like this
SELECT TOP 1 1 AS SortOrder, "Field1" AS Field1,
"Field2" AS Field2, "File #" AS [File #]
FROM MyTable;
but use your real field names instead of mine. If you have spaces or
special characters in the field names, enclose the field names (the ones
after the AS, not the ones in quotes) in [square brackets]. The fields
must be in the same order as in the first query.
4) Once this second query is returning the SortOrder value and the field
names, switch to SQL view. Delete the semicolon at the end, hit Enter to
move to a new line, type
UNION
and paste in the SQL from the first query. Save the result. This will
give you a query that returns the data and the header, in no particular
order but with the SortOrder dummy field that lets you get them in the
right order.
5) Create a select query based on the union query. Include all the
fields (individually, not with the * shortcut). Sort on SortOrder, but
clear the "show" checkbox for this field. Sort on other fields as well
if you want. When you run this query it should give you the header
followed by the records.
6) Export this last query.
Alternatively (if you prefer VBA), open a recordset on your table. Then
use the VBA code and the Basic file-handling statements (Open, Print #
etc.) to assemble and output first the header and then each of the
records directly to the file.
Hi John. Sadly, there is nothing in the data that will
allow me to sort to ensure that my special header record
is at the top of the output file 100% of the time. I'll
need one of those other methods that you referenced in
your note.
Thanks for the assist.
Regards,
LSJ
-----Original Message-----
Hi Larry,
Any time you need to be sure that Access will deliver records in a
particular order, you have to specify that order explicitly. Just create
a query that sorts the records with your special header record first,
and then export that query.
Post back here if there's nothing in the data that lets you achieve this
sort order: there are a few other ways round this problem.
When I use the "Transfertext" macro command, my field
name "File #" is exported to ".CSV" format as "File .".
The field name must be "File #" as is required by the
final user of the ".CSV" file as it gets subsequently
uploaded into anoth application. (No flexibility possible)
I thought that I could get around this by putting my field
headers as record 1 in the table and telling
the "Transfertext" specification that no field headers
exist. This works and "File #" appears in the ".CSV"
output file but there is a problem. The "Transfertext"
macro function's sorting is placing my dummy field header
record at the bottom of my ".CSV" file and I need it, as
it is field headers that will be read by an importation
program, to appear at the top of the file. All
attempts
to
get my dummy field header to appear at the top of the file
have failed.
Any assistance with this would be greatly appreciated!
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
