Solved

MS Access

Posted on 2013-06-14
8
192 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

I wanted to share this with fellow Experts, who might not know. How often have you wanted to learn something, only to be set back by either restrictions imposed on "trial" or "evaluation" software?  How often have you had to rebuild a home networ…
This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

785 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