Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Salem, New Hampshire, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ASCII 09 Tab exports (Access 97)

    I have a report that consists of strings of data with imbedded "TAB" codes chr(9). I am using a macro using the Output To command to create a MS-DOS text format file. When I open the exported file, the "TAB" has been converted to a "space". I have a second product which needs the TAB delimiter so this file is not usable. I notice if I export a file ifrom Excel, the results do have a TAB delimiter. How can I get a real TAB delimiter to end up in the output file vs. a converted tab-to-space?
    A short sample code from a text box: ="NAME" & Chr(9) & "PHONE" & chr(9) & "AGE" & chr(13) & chr(10)

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

    Re: ASCII 09 Tab exports (Access 97)

    When you export the file from Excel to *what*? If you export it to a tab delimited file, you'll get a tab delimited text file. If not, then not. If you output to an MS-DOS text file from Access, you don't get tabs. If you want tabs, you have to export to a Word mailmerge format.
    Charlotte

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    Salem, New Hampshire, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASCII 09 Tab exports (Access 97)

    Hi Charlotte. Thanks for your suggestion. You suggested "If you want tabs, you have to export to a Word mailmerge format." That returns to my basic question, how do I do that with a report?. Can you briefly explain the mechanics/code to send a report to a "Word mailmerge format"? The only option legally available with the OutputTo command is the MS-DOS Text format. I have tried Excel and RTF anyway, but they do not work either. I have tried using vbTab, and Tab() instead of chr(9), but they don't work either. (My export FROM Excel was only done to see if Excel also converted tabs to spaces, it did not. Discussion about Excel export is not really relevant to my needed solution from Access).)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASCII 09 Tab exports (Access 97)

    Do you have to export the report?
    Can't you export a table or a query with the DoCmd.TransferText ? There you can use the tab delimited format.
    Francois

  5. #5
    Lounger
    Join Date
    Apr 2002
    Location
    Salem, New Hampshire, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASCII 09 Tab exports (Access 97)

    Yes, it must be a report. I need to isolate some info for header and footer only, with details in between. I did try your suggestion about the table for my own knowledge, and that does keep the tab. I also saw Charlotte's output mode for Word Merge format, but that also is only for tables, not for reports.

    I could probably create three queries, output to tab delimited files, and then try to combine the files. It would actually be easier to use chr(16) vs. chr(9), and then do a Seard/Replace in that document. (That actually works, I am doing that now.) But I am looking for a solution that produces the final product from Access without having to introduce another step.

    Does it help to know that I believe UNIX uses a "regular expression" of t to represent tabs. Can that be created somehow in a string? Just trying to provide additional info to help find a solution.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASCII 09 Tab exports (Access 97)

    If you're not afraid of writing some code, you could write code to write the file directly.
    Here is a sample of a function to write the four first fields with the fields names of a table or query to a file.
    <pre>Function tabexport()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("test", dbOpenSnapshot)
    Open "test.txt" For Output As #1
    Print #1, rst.Fields(0).Name & vbTab & rst.Fields(1).Name & _
    vbTab & rst.Fields(2).Name & vbTab & rst.Fields(3).Name
    rst.MoveFirst
    Do While Not rst.EOF
    Print #1, rst.Fields(0) & vbTab & rst.Fields(1) & _
    vbTab & rst.Fields(2) & vbTab & rst.Fields(3)
    rst.MoveNext
    Loop
    Close #1
    End Function</pre>

    Francois

  7. #7
    Lounger
    Join Date
    Apr 2002
    Location
    Salem, New Hampshire, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASCII 09 Tab exports (Access 97)

    PERFECT! Thank you Francois. After figuring out the variables to check this out, it worked perfectly.

    I spent the past few hours playing with your sample code to figure out to make the Report Header and Report Footer. The loop in your example provides the Details, now I only have to figure out to do the Groupings, but I am sure I can figure that out. It will just require another DoWhile command based on the value of a Group Field being true or false.

    I made this function trigger on the Report "OnActivate" Event. This creates the text file immediately. The only issue then was how to stop the Report Window from opening? Simple....since it is all in code, I don't need the report module any longer, I just made a single line macro calling the function. THANKS again......very happy here!

Posting Permissions

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