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

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.

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

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

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

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.

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

1. Let's assume the Data Source for your Report is named tblTest and looks as such:
   
   Expand|Select|Wrap|Line Numbers

   1. Field1    Field2       Field3       Field4    Field5
   2. X2        123456790    11/4/2009    1359      09
   3. Y3        223456781    11/4/2009    899       09
   4. 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:
   
   Expand|Select|Wrap|Line Numbers

   1. Dim MyDB As DAO.Database
   2. Dim rst As DAO.Recordset
   3.  
   4. Set MyDB = CurrentDb
   5. Set rst = MyDB.OpenRecordset("tblTest", dbOpenForwardOnly)
   6.  
   7. Open "C:\Output.txt" For Output As #1
   8.  
   9. With rst
   10.   Do While Not .EOF
   11.     Print #1, ![Field1] & ![Field2] & Format$(![Field3], "mmddyyyy") & _
   12.                Format$(![Field4], "00000") & ![Field5] & "0100"
   13.     .MoveNext
   14.   Loop
   15. End With
   16.  
   17. rst.Close
   18. Set rst = Nothing
   19.  
   20. Close #1
3. Contents of Output.txt
   
   Expand|Select|Wrap|Line Numbers

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

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

Thanks, I will try it

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

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.

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

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 :
It really isn't necessary to re-invent the wheel.

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

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

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

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