Solved

MS Access

Posted on 2013-06-14
8
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

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…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

756 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