Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports: Writing directly to text (VB & Acces '97)

    I'm working a project where I need to output a report from Access '97 to text for upload to Mobius - a mainframe-based report viewing utility - and I just can't seem to get control over the report.

    I created a report within Access and got it 90% right, but I keep getting extra lines intermittently inserted when I export to .txt type (the only type that we can put _into_ Mobius for viewing. The second major problem is that I need to be able to put in a CR-LF: the text file has the breaks where the reports says it should, but when printed from text or viewed in Mobius, the pages don't break right.

    Via discussion with the other developers, we decided that the best solution would be to _programmatically_ write the report, line by line, directly into a text file. Unfortunately, this is something none of us have ever had to do before. NOTE: If Access can do what we need, please don't hessitate to let me know!

    For a look at what we want to end up with, please take a look at the included report. Keep in mind that, for Mobius, one page = 60 lines. Please see the bitmap (I'll attach it to a second post under this subject): the circled areas are hard-coded, the rest is all from the reports source - a query.

    I'd have to loop through, creating the header lines and spacing within the program, also checking to make sure that a given group of transactions would fit in the remaining lines and, if not, insert a CR-LF, a new header block, and then the next group of transactions.

    I basically need an example that shows how to write both records and hard-coded text string sequentially to a file.

    Little help? Thanks.
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports: Writing directly to text(here is bmp)

    Here's the .bmp as promised in the main post...
    Attached Files Attached Files

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Reports: Writing directly to text (VB & Acces '97)

    Maybe you aren't paid to question requests like this, but why would anyone want you to reformat Access reports for viewing in a mainframe report viewer? Don't they know about snapshots (.snp files)??

    With VB/VBA, you can write text files either using the old Open/Print/Close syntax or the equivalent methods of the FileSystemObject in the scripting library. I wrote some code for "wrapping" a string of arbitrary length to fixed width records, but it isn't particularly elegant: it laboriously adds one word at a time to a strong variable (which starts with a number of spaces for the indent) up to, but not beyond, the maximum line length, then starts a new line, etc., until the text to be wrapped has been exhausted. (The code is too ugly to post.) Hopefully you will develop something better.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Reports: Writing directly to text (VB & Acces '97)

    Did you consider exporting the data from your records into a fixed length text file? Access can export records in either a delimited or fixed length format. Lines are terminated by a CR/LF. We've done something similar where we then open the file in Word and create Headers and Footers, and then stuff it back into Access as a fixed record length file. The "Output to File" will also work, but it is majorly tedious. We usually try to find the quickest and simplest way, and text files usually are.
    Wendell

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

    Re: Reports: Writing directly to text (VB & Acces '97)

    Excuse me, but where does VB come into this? This sounds like a pure Access question. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    For future reference, it's better to post Access-specific questions to the Access board. Access is different enough in its implementation of VBA and in its object model that you'll usually get faster answers in the Access forum. If you have a question about using VB with Access, then this is definitely the right place. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  6. #6
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports: Writing directly to text (VB & Acces '97)

    I apologise that I apparently wasn't clear enough in my first post (seems to be a habbit...I promise I'm trying to get better [img]/forums/images/smilies/wink.gif[/img] ). Taking the points, thus far, in order:

    jscher2000: I am, indeed paid to _question_ such things, but, out here on the perimiter (thanx, Jim), we are also paid to _accept_. Corp HQ has said "thou shalt use Mobius for distributed document viewing" and, while I'm paid to question, beating on HQ's doors too long could garner the wrong kind of attention in times like these. Also, I'd never heard of snapshot (.snp) files; I'll definitely take a closer look there.

    WendellB: The length of the lines isn't the problem, it's the sporadic spaces that the Access report, output to text exhibits and getting the page-breaks to take. I probably should have mentioned this before but, while the text-file example I attached is where we want to end up, it is far from what we actually _get_.

    Charlotte: This will be an Access program but the output of the reports is probably going to have to be line-by-line in VBA, unless Mobius can take in the .snp format (like I said, until jscher2000 menioned it, I'd never heard of it).

    All that said, this isn't all bad: this is probably something it wouldn't hurt to be able to do, anyway! (forced grin)
    <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

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

    Re: Reports: Writing directly to text (VB & Acces '97)

    <hr>output of the reports is probably going to have to be line-by-line in VBA<hr>
    VBA from where? VBA only runs within an application or within the VB executable. If you're running code from VB, that's one thing. If you're running VBA code from within Access, that's something entirely different. You'll get better answers if you make that distinction completely clear, because the answers will be different depending on which it is.
    Charlotte

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Reports: Writing directly to text (VB & Acces '97)

    Snapshots are viewed with the Snapshot viewer. Great fidelity, just like the preview window in Access. Almost certainly not transferable to Mobius. Perhaps, though, you can trick someone important into liking the Snapshot viewer and pushing the rock up the hill for you.
    <UL><LI>About report snapshots and Snapshot Viewer
    <LI>Microsoft Snapshot Viewer Available in Download Center (Q175274)[/list]Hope this helps.

Posting Permissions

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