By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Exporting from Access to Text file

P: 1
I have an Access97 data base where we are exporting records out to a text file that is then sent to a customer via EDI transmission. The problem I am having is that during the export process access is converting my number fields to scientific notation and they are getting bounced by our customer's EDI program. Example is .0027 is showing in the text file as 2.7E-3. I have tried setting formatting to fixed with 4 decimals, general with 4, no formatting, single and double...... basically anything and everything I can think of.

I have tried exporting a table. I have tried exporting a query. The results are the same. If anyone can tell me how to export this to a text file and maintain the formatting without converting to scientific notation I would appreciate it immensely.
Apr 28 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
Hi Marty. Very strange that you are running into a format problem when you've tried specifying fixed format. Anyway, as a workaround, create a query which includes all the fields from your table, and replace the fields which are giving you the export difficulty with calculated fields as in the example below, substituting the name of the problem fields where you see name of your field below:

Fixedformat1: Format([name of your field], "0.0000")

Give each calculated field an appropriate name. It cannot be the same as the original field.

Save this query then export the query to a text file as before.

The Format function returns a text string representing the number in the format specified. I mention this to explain that as the value created by Format is already a text value whatever is happening in the export cannot of itself revert this value to numeric in scientific notation.

-Stewart

ps Did you try creating a Format Specification for your export file?
Apr 28 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.