473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Exporting from Access to Text file

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
1 4873
Stewart Ross
2,545 Expert Mod 2GB
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

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

Similar topics

5
by: Jayjay | last post by:
Is it possible to export a report to word and have it include the graphics that are in the report? I'd like to take some of our reports and export them to word so we can email those to the...
3
by: Fran Zablocki | last post by:
I have a process that exports an Access table to a comma-delimited text file. One of the fields that is exported shows the date it was exported, using the Date() function. In the Access table, the...
4
by: D | last post by:
I've created a report with many subreports of aggregate data. I want my client to be able to export this data to Excel to make her charts, etc. Only one problem: one of the fields is a "SchoolYear"...
2
by: ASIF | last post by:
Does anyone klnow of a quick way of exporting data from access to a text doucument, the data needs to be tab seperated, and the data to be exported needs to be selected using parameter queries. I...
2
by: Kenneth | last post by:
How do I remove the limitation in Access that deny me from exporting 24000 rows and 17 columns (in a query) into Excel? Kenneth
6
by: J. Shrimp, Jr. | last post by:
Following code exports tables as text files: For Each tdf In db.TableDefs StrTblName = tdf.Name Me.txtProgName = StrTblName Me.txtProgName.Requery tblAtt = tdf.Attributes moddate =...
1
by: nunYa | last post by:
I am trying to use the DoCmd.TransferText method to export an access table to a Tab Delimited Text File. I have put the code behind the onclick event of a button and keep getting the error...
16
by: pmatzek | last post by:
I am attempting to automate a process involving importing a .txt file into an existing table, changing a date in each record, then exporting the table, again as a .txt file. One field contains a...
8
by: paquer | last post by:
I'd like to provide an Export Function from my forms, where the User can choose the File name & save location Although I cannot get the "Save As" dialog box to open properly from access. What is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.