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 HRF10132009000013

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.
X21234567901012200901359010110

I would appreciate some VBA code to do this.

[ChipR]

You should find this tutorial helpful: Using The FileSystemObject 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]

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