File Export "sort" issues

[jgelpi16]

I have the below code that runs to export a text file. I have both the Pgarn01 query and the table set to Ascending order by ID. However, when the export occurs a strange thing happens. The data will display as

header record
record 5
record 6
record 7
record 8
footer record
record 1


record 2
record 3
record 4

The expected result (and how it is displayed in both the query and table) is:
header record
record 1
record 2
record 3
record 4
record 5
record 6
record 7
record 8
footer record

The header record has "000000000" as the value in the ID field and the footer record has "999999999" as the ID field value.

What is going on?

Private Sub cmdExportPgarn_Click()
Dim varHeaderNme As String
Dim varHeaderSSN As String
Dim varQueryName As String
Dim varSpecName As String
Dim varTableName As String
Dim varFileName As String
Dim varTextFile As String
Dim varHeaderDate
Dim varBankName As String
Dim varBankCode As String
Dim varNewFolder As String
Dim varFileRename As String
varBankCode = cboBankName.Column(2)
varBankName = cboBankName.Column(1)
'**********pgarn.txt
varHeaderNme = "***From: NCDOR; To: " & varBankName & "; pgarn" & varBankCode & ".txt***"
varHeaderSSN = "000000000"
varHeaderDate = Format(Date, "MM/DD/YYYY")
DoCmd.SetWarnings False
varQueryName = "Pgarn00"
DoCmd.OpenQuery varQueryName, acViewNormal
mySQL = "INSERT INTO AAtblBankExport (EntityID, FullName, GarnDate) VALUES ('" & varHeaderSSN & "', '" & varHeaderNme & "', #" & varHeaderDate & "#)"
CurrentDb.Execute mySQL, dbFailOnError
varQueryName = "Pgarn01"
DoCmd.OpenQuery varQueryName, acViewNormal
varQueryName = "PGarnFooter"
DoCmd.OpenQuery varQueryName, acViewNormal
DoCmd.SetWarnings True
varSpecName = "AAtblBankExport Export Specification"
varTextFile = "pgarn" & varBankCode & ".txt"
varFileRename = "pgarn" & varBankCode
varTableName = "AAtblBankExport"
varNewFolder = "C:\File\Archive\" & varFileRename & Format(Date, "MMDDYYYY") & ".txt"
varFileName = "C:\File\" & varTextFile
Name varFileName As varNewFolder
DoCmd.TransferText acExportFixed, varSpecName, varTableName, varFileName
End Sub

[NTC]

hmmm well off the cuff, does the export spec have the ability to do OrderBy ? I honestly can't remember. you would want to look into that.

only the table and the export line of the code count here - everything else is prelude.

you mention an ID field that is being used to sort the exported table correctly. does that ID field also get exported? if not - you might try an experiment in having the table sorted correct but without that ID field existing. rewrite it to a new table without that field. don't mean to send you on a wild goose chase but it might give you some insight....

is a little odd; one could do a post process text rearrange in vb but one would want to avoid that....

also as a sanity check I might just make a new export spec for that table - and run it manually to see what happens. have done plenty of these and have never had an order problem - but on the other hand the order often doesn't matter so maybe I didn't inspect it closely. thinking back on one project that involved a header/footer I recollect that I exported those all as separate files (header, body, footer) and then glued them together using vb...but that was needed for coincidental reasons as I couldn't get them all into one record set.

hope this helps a little.

[jgelpi16]

Thanks for the help. I don't see a sort in the import spec. The ID field is being export to the text file. I guess to me the perplexing thing is the data shows up just fine in the table and query. It is not until I export the data that it gets messed up. Have you hever heard of this issue pertaining to network drives? I am exporting to a network drive so maybe there is a network interruption that is causing this? It hasn't done it in a while now so maybe it was just a fluke? Thanks for the help.

[RuralGuy]

Don't export a table. Export a query with an OrderBy clause!

[jgelpi16]

Ok, that will work with one exception.....I need to OrderBy FileCode and then by ID.

i.e.....

ID FileCode
1 a
2 a
3 a
4 a
1 b
2 b
3 b
4 b

This may be simple but I can't get it to work.

[RuralGuy]

OrderBy FileCode; ID

[jgelpi16]

Thank you!...One problem. When I do that I get an error message stating "Characters after end of SQL statement" or something like that.

[RuralGuy]

Is this thread ready for you to follow the link in my sig and mark as Solved?

[jgelpi16]

Not quite yet....

[jgelpi16]

When I use the Query Builder, or whatever you want to call it, and then look at the SQL it has it as follows:

ORDER BY FileCode, ID;

[RuralGuy]

Oops, my bad. comma then semicolon.

[jgelpi16]

That does not work. When I run that it orders it entirely by FileCode and disregards the ID sort.

[RuralGuy]

Sorry but I have to leave for several hours. You are saying it does not sort correctly just looking at the Datasheet mode of the query? Is your OS and Office up to date on patches?

[jgelpi16]

I am operating Office 2007. All patches, etc are up to date. When I look in Datasheet mode it sorts based upon FileCode and does not sort by ID. Thanks for your help.

[RuralGuy]

Are you talking about the Datasheet view of the Query or the Table? Can you post the SQL for the Query so we can see it please?