When exporting data from a query, ACCESS is changing the data! 2

[KHerm]

I have a query that I need to export as a fixed length flat file. I have chosen, TXT, CSV, and ASC output formats with the same odd result. My first column is a constant "222222". I have several date columns that I must format as 10 characters MM/DD/YYYY. When I look at the result of my query in datasheet view, everything looks fine. Then I do the following steps:

1. I choose, FILE>SAVE AS/EXPORT
2. I choose "To an External File or Database", click OK
3. I change "Save as type:" to "Text file (*txt,*csv,*tab,*asc)"
4. I type in file name: "OUTPUT.CSV" and click "Export"
5. I choose "Fixed Width"

At this point Access changes my data. My column that is supposed to be "222222" is now "22222" (quotes don't show up in the Sample export window) and my dates no longer look like "08/09/2000" but something like "03/0903/09". The output to the flat file is wrong too.

I get the same data change whether I use *txt or *asc

I am running Access97 on NT. The same thing happens in the same database on a Windows98 machine.

I sure hope someone recoginizes this anomaly and knows how to fix it.

Thanks in advance!

Ken

 

[DougP]

All you can do is create your own export function.

Open your recordset
Open the text file
loop through it
do while not rst.eof
' and format the field the way you want it.
MyDate = format(rst!Mydate,"mm/dd/yyyy&quot
MyNum = format(rst!MyNumber,"######&quot
create a string by adding each field
MyString = MyNum &"," MyDate
Print #1, MyString
loop
close text file
close rst
close db
'this is a very brief example
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[KHerm]

Thanks, Doug,

I wasn't a Visual Basic person, but I'm sure turning into one! As I worked on this, I discovered that the data wasn't changing at all. Access just couldn't calulate the width of the field without manual help. The function is going to have to fix that, because we are going to do this process every month and every column would have to be adjusted by hand. No way!

Thanks again,

Ken

 

[MichaelRed]

Ken,

You should look at the "transfertext" function. It provides access to the proper method for exporting the information - without the hassle of all the selection. an important part of this - in exporting text - is the creation of an Import/Export specification. Help will tell you 'how to' for this - but not how/where to get the sample for use in building the spec. I usually 'go through the motions' and do the manual export as you describe, ubtill the exported file is 'correct'. Then use this as the sample to inport, building the specification with the sample file. BE SURE to save the Specification w/ a name (in the advanced part). Put the specification name (as it was saved!) in the transfertext command.

Put the transfertext command in a simple "Module/Procedure" and call it when desired. A Sample:

Public Function basExportJobs()

    DoCmd.TransferText acExportFixed, "YourSpecHere", "tblJobsApplied", "Jobs.Txt"

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[KHerm]

Thanks, Mike!
I'll definately be working on this one.

Ken