asked on

MS Access

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!

terencino

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

Gary Dewrell

I have not played with Access in many years but take a look at DoCmd.TransferText method.

elletter

ASKER

Thanks terencino, i'll try it and let you know.

elletter

ASKER

When i tried to export the query it also exports the headers and I don't see an advance option.

terencino

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.

The 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

elletter

ASKER

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

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

ASKER CERTIFIED SOLUTION

terencino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

elletter

ASKER

Thanks Terry that did the trick.