DoCmd.TransferText acExportDelim having weird results

[Isaac]

I have some code I've been running for weeks, if not over a month. One line:

DoCmd.TransferText acExportDelim, , "qrySelectionForETL2", strPath, True

The line executes and the text file is created successfully. However, inside the text file, where the query would show a normal line of values, the text output is showing these numeric values:

14 00 00 00 ,14 00 00 00 ,14 00 00 00 ,14 00 00 00 ,59 00 00 00 ,59 00 00 00 ,59 00 00 00

Anyone else ever seen this? What in the world is going on?

 

[The_Doc_Man]

To be specific, are we talking about having digits in groups of 2 digits plus a space?

First thing I would look at would be if there is any kind of output formatting in the named query and perhaps with hidden spaces.

The usual question would be in several parts. First, what would the raw output look like. Second, what would your desired output look like. Third, show us the query and in particular, look at the field properties for the fields in that query.

 

[Isaac]

They are just text values, in this case specifically names or 4 digit numbers, so the output should be more like:

Suzie Johnson Bill Johnson James Johnson Someone Else Someone Else ####, ####

But instead, these seemingly random numbers in groups of 2 digits with spaces, are being created. The data even looks right when I right click, the query, export to text, and preview it!

These fields are Sharepoint People fields. typically they return, when queried, either a name or a 4 digit number, either one of which would have been fine for me.

Putting off showing the query hoping someone has an idea before that because the query would give away a lot about where I work and I'd have to sanitize almost every bit of text in it

Something about code pages maybe on my machine?

 

[The_Doc_Man]

Sorry, got no clue. If you open the query and it is OK, then the export should be fine. If it is not, then the only other thing I could imagine would be some kind of corruption of Access. If you have the disk from which Access was installed, try to re-install it and take the "repair" option.

The fact that it is SharePoint blocks me from further help in that direction. I have no experience with it.

 

[Pat Hartman]

Export the file ONCE manually to create an export spec. Click on the Advanced button to set some formatting options and save.
Reference the export spec in your TransferText command.

 

[AccessBlaster]

Is there a double comma after DoCmd.TransferText acExportDelim or is that a typo?

DoCmd.TransferText method (Access)

Office VBA reference topic

docs.microsoft.com

 

[Isaac]

Is there a double comma after DoCmd.TransferText acExportDelim or is that a typo?

DoCmd.TransferText method (Access)

Office VBA reference topic

docs.microsoft.com

Yes I left the optional spec name argument blank.
May have to try it with a named spec as Pat suggested