Solved

MS Access

Posted on 2013-06-14
8
183 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

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 …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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 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…

743 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

12 Experts available now in Live!

Get 1:1 Help Now