Exporting to Text File

[mjklunk]

I am having issues when exporting records to a comma delimited text file. I have 4 numeric fields that are having the formatting changing during the export process. Below is a sample. My numeric fields are of double size and fixed at 4 decimals with the exception of the final column which is fixed at 2 decimal places. For whatever reason, occasionally a field will display as scientific notation which causes an EDI transmission between our customer and and us to fail. I have tried exporting a table as well as a query and changed formatting with no luck. If someone could tell me how to get around this I would appreciate it. My original application is in Access97 but I have experienced the same thing in Access2003.

DM1234,5/31/2008,23,188.49,A520,+,2.7e-03,CW,480.40,CW,1.29
DM1234,5/31/2008,23,188.49,B123,-,0.36,CW,520.00,CW,187.20

 

[boblarson]

You should be using an Export specification.

See here for how to build one:
http://www.btabdevelopment.com/main...teanExportSpecification/tabid/64/Default.aspx

 

[mjklunk]

I am using an Export Specification. Here is the code:

DoCmd.TransferText acExportDelim, "Standard_Spec", _
"qryFilteredEDIForRowCount_2003", "C:\EDI\" & strFilename

Standard_Spec is my Specification

 

[DCrake]

Simple Software Solutions

If you are using a table for for the root of your export spec that change this to a query which references this table and the rounding or decimal places to the desired length.

CodeMaster:

 

[mjklunk]

If you are using a table for for the root of your export spec that change this to a query which references this table and the rounding or decimal places to the desired length.

CodeMaster:

I'm sorry, I don't understand what you just said. If it would help I can attach screenprints or code that show what is going on.

 

[DCrake]

Simple Software Solutions

Ok

Edit the query qryFilteredEDIForRowCount_2003

find the offending column and change it read Expr1:Round(YourFieldName,4) or Expr1:Round(YourFieldName,2) as the case may be.

This will force it to only export the data with 2 or 4 decimal places.

David

 

[mjklunk]

Ok

Edit the query qryFilteredEDIForRowCount_2003

find the offending column and change it read Expr1:Round(YourFieldName,4) or Expr1:Round(YourFieldName,2) as the case may be.

This will force it to only export the data with 2 or 4 decimal places.

David

Ahhh Thank you I will try that right now and let you know how it works.

 

[mjklunk]

One thing I have discovered (or maybe my system isn't working correctly) I can only create a export spec from a table. When I attempt to do it using a query I get an error of "Too few parameters." I gave up on getting the number fields to format correctly and created a table with all text fields then converted all the numeric fields to text fields in the query calling the export. Certainly not the way I think it should have to be done but it is working for the moment.

 

[CraigDolphin]

One thing I have discovered (or maybe my system isn't working correctly) I can only create a export spec from a table. When I attempt to do it using a query I get an error of "Too few parameters."

You can work around this by wrapping the parameter reference in an Eval() function

Thus, instead of:
[Forms]![FormName]![ControlName]
use...
Eval("[Forms]![FormName]![ControlName]")
in the criteria of your query.

You can also force the format of the query field to be fixed in the query by using the format function:
Thus, instead of
[FieldName]
use...
Format([FieldName],"#0.0000")
...or however many decimals you want, and you should be able to export without the sci.notation etc

 

[boblarson]

I'm just wondering how they ended up with a parameter required when they were just going to do a query from a table so that they could add rounding. Something doesn't seem right there...

 

[CraigDolphin]

Hmm. I didn't get that from my casual reading of the OP but you may be right. You usually are!

I have a stored query used to export data in flat file format for import to the EPA's db that uses parameters to break the data into discreet chunks and ran into similar issues at the time I set it up.

I assumed it was a similar situation with the OP.