Solved

MS Access

Posted on 2013-06-14
8
188 Views
Last Modified: 2013-06-24
Good afternoon all,

I created an Access program that generates different reports for a customer.  Now I was asked if I could generate a text file for EDI purposes.  I have a query that includes all necessary fields but I have no idea how to generate that text file.

Below are the parameters of the download file I need to create:

AII ID      Length      Start      End      Decs
                    2        1       2      
AECST#            5      3      7      
AEFIL#            8      8      15      0
AEPTEX            4      16      19      0
AECREF          15      20      34      
AEMODE            2      35      36      0
AEDEST            2      37      38      
AESTOR            2      39      40      
AEETD            8      41      48      
AECACD            4      49      52      
AERELT            1      53      53      
AEVESS          23      54      76      
AEPOUL            5      77      81      0
AEFLAG            2      82      83      
AEHAZM            1      84      84      
AEIBCD            2      85      86      0
AEIBNO           15      87      101      
AEBKNO          30      102      131      
AEWVR            1      132      132      
AEDEMP            3      133      135      

The record length is 265 bytes,  there are 4 lines in total the above is just for the first line.  This is an example of how it should look:

T112245200000002402J388516        30MXTX        CGAENCALIFORNIAGASTRANSPORT         Y                                               N157

Like I said, I got the query ready I just need help with the output.  Please let me know if you need more details.  Hope you can help!
0
Comment
Question by:elletter
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:terencino
ID: 39249255
Hi looks like you have done all the hard work already. It is slightly different for each version of Access, but just right-click on the query, select Export, select the Text file option, wait for the Export Text Wizard to pop up, select Fixed Width, then click on the Advanced button to specify the column widths for each field (sames as the list of parameters you have already), save the specification, then proceed with the export.

Let me know if you have problems (and your version of Access) and I'll provide you with a step-by-step, although I suspect you will be Ok
...Terry
0
 
LVL 12

Expert Comment

by:Gary Dewrell
ID: 39249257
I have not played with Access in many years but take a look at DoCmd.TransferText method.
0
 

Author Comment

by:elletter
ID: 39249265
Thanks terencino, i'll try it and let you know.
0
 

Author Comment

by:elletter
ID: 39254644
When i tried to export the query it also exports the headers and I don't see an advance option.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 16

Expert Comment

by:terencino
ID: 39254771
Hi what version Access do you have? You will need to create the specification if you are going to export via code, which you need to do to get the text file without headers. The picture below shows the Advanced button at the bottom of the Export Text Wizard dialog.
Export SpecThe code would be something like this, using DoCmd.TransferText. The False argument specifies no headers.
Sub ExportQueryX()
DoCmd.TransferText acExportFixed, "Specification", "QueryName", "Path_and_FileName.TXT", False
End Sub

Open in new window

0
 

Author Comment

by:elletter
ID: 39266605
That is exactly what i need terencino, but i'm not getting that wizard.  I'm running access 2010, this  is what i get:

 
Export wizard

If i click next i will get an error: Too fiew parameters. Expected 1
Qry-Export-file.bmp
0
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 39269008
Does the query require input like a date or something before it runs? If so might need a two step process, to create a table from the query, and then export the table data.
...Terry
0
 

Author Closing Comment

by:elletter
ID: 39273185
Thanks Terry that did the trick.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Share codes 68 119
Excel Hangs / Not enough Memory 5 85
How to check if column "Assigned To" group was changed in SharePoint 2010? 18 51
Display multiple images in report 12 107
Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now