Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Output Report to Fixed-Width text file? (97)

    I have an Access 97 Report that has one Group header with Detail. I need to output to a text file that is a fixed-width file. The outputting is working okay, it's the fixed-width aspect that's the sticky part.

    Any ideas? I am only a beginner with VBA, but I can use it to resolve this if you explain it clearly.

    Thanks in advance!
    -Sharon

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Output Report to Fixed-Width text file? (97)

    Use acExportFixed as the first argument in your TransferText statement. For fixed width import/export, you also need to create an import/export spec that contains the exact width you want to apply to each column. The easiest way to create that spec is to try to import a file and select fixed width format. You can set the individual column widths and the Advanced button will allow you to save the spec with whatever name is appropriate. Then you pass the name of the spec in your TransferText statement.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output Report to Fixed-Width text file? (97)

    Thanks for the quick response, Charlotte!

    Unfortunately, TransferText doesn't work with Reports. :-(

    I've tried using DoCmd.OutputTo. It works beautifully to create the text file. However, it's not addressing the fixed-width issue.

    I know at one point I even tried to use a fixed-width font like Courier for the report, and it comes close, but not perfect. I think the closest I got was the horizontal spacing was good, but there was a pattern of extra line returns, which I can probably deal with in Word. It may have also involved concatenation and trimming of extra spaces and fields, which is fine, but it seems "there must be a better way"!

    Is there perhaps a way using VBA and/or SQL to build a text string that could be exported to a text file??
    My text file needs to come out as follows, for example:

    ReportHeaderField1-35charactersxxxxRptHdrFld2-25charsxxxxxxxRptHdrFld3-30charsxxxxxxxxxxxx
    GroupHeaderField1-25charsGrpHdrFld2-20charsxxGrpHdrFld3-25charsxxxxxxx
    DetailField
    DetailField
    DetailField
    GroupHeaderField1-25charsGrpHdrFld2-20charsxxGrpHdrFld3-25charsxxxxxxx
    DetailField
    DetailField
    etc., etc.

    -Sharon

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Output Report to Fixed-Width text file? (97)

    Sorry. I focused on the fixed width question and totally space the reports part. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    The problem is that you can't really export reports to fixed width. Fixed width is used to export *data* to columns of a particular width so that you know a particular column starts at position x and the next column starts at position y, regardless of whether the data is that wide or not.

    Perhaps I don't understand what you're trying to accomplish. If you want to export the report to text, you have to be very careful about a number of things. For one thing, the height of the controls is going to have to be watched carefully, as well as the length of the lines. Any stray space at the end of the line that causes it to wrap will put in a blank line. You're right to chose a courier font, but you can't really control the "fixed width" part as closely as you might like. The width of the controls is what will determine the width of the data exported, and reports are not as precise in that respect as forms are. The controls will line up properly in the exported report but you'll have to play with line length and control height to eliminate the extra blank lines.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Output Report to Fixed-Width text file? (97)

    Ah, I found the post I had remembered. Take a look at this post for more information on what is required to eliminate those blank lines.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •