Export to fixed in 2007 - bug?

[phillips001]

I never had an issue with these type of things in 2003, but repeatable
difficulty comes up in 2007. I'm exporting to fixed position flat file.
Initial query does some formatting like stripping the decimal point and
formatting dates as needed, but exporting to text in 2007 will still retain a
format, even though table fields are Text, the formatting box is NOT checked,
and using a fixed length formatting specification. The wizard display looks
perfect, the end results do not. Anyone have similar issue?

 

[Ken Snell \(MVP\)]

Post the SQL statement of the query that you're exporting. How are you doing
the export -- File | Export? Or TransferText?

 

[phillips001]

Thanks, Ken:
Here is the query sql:
SELECT "P" AS TrxType, CRTX_0054_PaymentData.CsClientRef AS ChkControlNbr,
Format([CsPmtCompAmt]*100,"000000000") AS TrxAmt, "02/11/08" AS TrxDt,
Format([CheckNo],"00000") AS ChkNbr, CRTX_0054_PaymentData.CheckABARoute AS
BnkRtNbr, CRTX_0054_PaymentData.CheckABAAccount AS ChkAcctNbr INTO
CRTX_0054_PaymentFile
FROM CRTX_0054_PaymentData
WHERE (((CRTX_0054_PaymentData.CsClientRef)<>""));

I then take table and Export it as a text file. I have a fixed-width text
specification I attached to it, the first line reading FieldName:TrxType,
Start:1, Width:1.

My output results, however, contain formatted dates and amounts.

 

[phillips001]

Ken, the only way I've worked around this (I haven't done code because this
is not a regular job) is to open/design the table, change the text widths to
what they should be from the defaulted 255 width, export to text file, DON'T
use specifications, and it should come out okay. I could of typed it in
EDLIN for all the time I wasted. ...Chuck

 

[Brian Schmidt]

We have encountered exactly the same thing. We were exporting to a flat file
format, using an export specification, and all looked perfect until you
opened the resulting text file and found numbers formatted like your regional
settings (with extra decimals) and tons of extra trailing spaces.

What fixed it for us was to delete all of the export specifications and
recreate the one we were trying to use from scratch. These specs were
created in Access 2002 and perhaps they do not upgrade to 2007 correctly.
Once we did this we could even revert the field lengths in the table design
and it still exported correctly.

Hope that's helpful.

Brian