By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,162 Members | 801 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,162 IT Pros & Developers. It's quick & easy.

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

P: 5
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.
Nov 5 '09 #1

✓ answered by ADezii

  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?

Share this Question
Share on Google+
8 Replies

Expert 100+
P: 1,287
You should find this tutorial helpful: Using The FileSystemObject With VB and VBA - Part 3
Nov 5 '09 #2

Megalog
Expert 100+
P: 378
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.
Nov 5 '09 #3

ADezii
Expert 5K+
P: 8,736
  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?
Nov 5 '09 #4

P: 5
Thanks, I will try it
Nov 6 '09 #5

P: 5
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.
Nov 6 '09 #6

NeoPa
Expert Mod 15k+
P: 31,770
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 :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText
It really isn't necessary to re-invent the wheel.
Nov 8 '09 #7

P: 5
This does not answer the question of how to add multiple criteria to the print statement.
Nov 12 '09 #8

ADezii
Expert 5K+
P: 8,736
@jcgads
Kindly post an example of the 'Multiple Criteria' that you are referring to.
Nov 12 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.