[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MS Access

Posted on 2013-06-14
8
Medium Priority
?
210 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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 …

656 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