how to export the result of a report to a fixed length file

[jcgads]

In ACCESS 2007 I need the results of a report in a fixed format file format text file. The record length of the file is 80 characters. The first record is a header record. It has the format of

##SET14 space)today's date as a 8 digit date)a 6 character transaction count with leading zeros)

i.e.
##SET HRF101320090000 13

The data records have a format of

X2(a 9 digit Id)(today's date as a 8 digit date)(5 digit number)(0100 concatenated to 2 digit year)

i.e.
X21234567901012 200901359010110

I would appreciate some VBA code to do this.

[ChipR]

You should find this tutorial helpful: Using The FileSystemObjec t With VB and VBA - Part 3

[Megalog]

Welcome to Bytes, jcgads..

First off, I suspect you're wanting more than just a way to generate a text file. You seem to be asking for ways to generate string values out of multiple sets of data first. Or maybe you're saying you already have a report that you want to export the contents of to a specifically named text file? The description you gave can be translated in a few different ways, so think about your wording a bit more.

You also have to break down what you are asking for into smaller steps, each of which need to be completely detailed. Remember, we dont know anything about your processes or objectives.

This is explained a bit better in the Posting Guidelines. If you still need assistance, then posting a set of better questions will give a much better response.

[ADezii]

1. Let's assume the Data Source for your Report is named tblTest and looks as such:
   

   Field1	Field2	   Field3	   Field4	Field5
   X2	    123456790	11/4/2009	1359	  09
   Y3	    223456781	11/4/2009	899	   09
   H7	    222345699	11/4/2009	23456	 09

2. It's crude, but the following code will Output your Data in the approximate manner which you describe to C:\Output.txt:
   

   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("tblTest", dbOpenForwardOnly)
   
   Open "C:\Output.txt" For Output As #1
   
   With rst
     Do While Not .EOF
       Print #1, ![Field1] & ![Field2] & Format$(![Field3], "mmddyyyy") & _
                  Format$(![Field4], "00000") & ![Field5] & "0100"
       .MoveNext
     Loop
   End With
   
   rst.Close
   Set rst = Nothing
   
   Close #1

3. Contents of Output.txt
   

   X21234567901104200901359090100
   Y32234567811104200900899090100
   H72223456991104200923456090100

4. To be honest, your Post is a little confusing to me, but is this what you are after, or close to it?

[jcgads]

Thanks, I will try it

[jcgads]

it works when I change the code so so that it outputs what I need.

My remaining problem i that I cannot figure out how to add a second and third selection criteria based on a fields which are not in the report but is in the query.

[NeoPa]

As a general rule, when outputting data in record format, it is quite simple to design a query in exactly the right format (a single field laid out as required - fairly straightforward ) then output this using :

DoCmd.TransferText

It really isn't necessary to re-invent the wheel.

[jcgads]

This does not answer the question of how to add multiple criteria to the print statement.

[ADezii]

This does not answer the question of how to add multiple criteria to the print statement.

Kindly post an example of the 'Multiple Criteria' that you are referring to.