How to Export a Report to a TEXT file without extra line spacing

I have a database program that takes records from a table, sends it to a report, and exports that report to a text file.  I need the text file to format with ONLY single spacing between the records.  Currently the text file shows/prints with extra line spaces and I canot determine why.  I am attaching a copy of the text file (AS EXPORTED) and also text files (CORRECTED-How I need them to look)  One is a straight line text and the other file has columns that MUST line up with each item.  
Basically I need to remove (or not enter) line spaces for INVENTORY and line up the columns for SALES.
The CODE to export is:
  SALES:-->   DoCmd.OutputTo acOutputReport, "rptSalesDaily", acFormatTXT, ("C:\Users\juricta\Microsoft\Access\Database Files\Working\LiquorReports\" & vOLCCFileName)

INVENTORY--> DoCmd.OutputTo acOutputReport, "rptSalesDailyInventoryConvertToString", acFormatTXT, ("C:\Users\juricta\Microsoft\Access\Database Files\Working\LiquorReports\" & vOLCCFileName)

SALES Table Contents (only a few records)
tblSalesDaily
Product Code      Product Name      List Price      QTY        Price Per   Total Purchase
001312      MONARCH BOURBON-A-BL      $9.00      2      $18.00      $18.00
001402      JIM BEAM                           $19.95      1      $19.95      $37.95
001382      ELIJAH CRAIG                        $24.95      2      $49.90      $49.90
001402      JIM BEAM                                          $19.95      2      $39.90      $89.80
001482      GENTLEMAN JACK                        $31.95      2      $63.90      $63.90
001462      JACK DANIEL #7 BL LA      $22.95      3      $68.85      $132.75
001466      JACK DANIEL #7 BL LA      $2.75      1      $2.75      $2.75
001392      WOODFORD RESERVE      $36.95      2      $73.90      $73.90

INVENTORY Table Contents (only a few records)
tblSalesDailyInventoryConvertToString
ID      StringExportVariable
1      14120919041009OLF00021.FIL 00
2      0014120919041009OLF00063 00
3      631412091904100900700015141209
4      631412091904100900800030141209
5      631412091904100901100005141209
6      631412091904100900109200006141209
7      631412091904100900129200000141209
8      631412091904100900130200003141209



If you need any other information please do not hesitate to ask.
DailyInv141209---As-Exported.txt
DailyInv141209---Corrected.txt
Sales141209---As-Exported.txt
Sales141209---Corrected.txt
jurictaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
fabriciofonsecaConnect With a Mentor Commented:
You get the quotation marks, dashes between records, and "PIPE | marks" because you are not using a specification for the transfer text command.

Try the following:  
        DoCmd.TransferText acExportDelim, "ExportSpec" , "tblSalesDailySalesExport", "c:\Users\juricta\Microsoft\Access\Database Files\Working\LiquorReports\bubba31.txt"

  To create the "ExportSpec" follow the steps below:

  1-) Right Click on the tblSalesDailySalesExport with the Mouse
  2-) Export -> Text File
  3-) Select any export name and Click "OK"
  4-) Click "Advanced" button (left bottom of the page)
  5-) In the Advanced screen you can set or clean quotation marks, dashes between records, and "PIPE | marks", etc
  6-) Click "Save As" and save with the following name: "ExportSpec". This is the name you will use in the cmd.transfertext command.

  I hope it can help.

Regards,
0
 
SimonCommented:
Hi, neither of your '--Corrected.txt' files are available. I can see the '--As-Exported' files but I get 404 errors for the others.

Can I ask why you want this particular type of output rather than tab or comma delimited text or PDF or Excel? Is it a requirement of some downstream system?
0
 
fabriciofonsecaCommented:
Try to export the table which is feeding the report and not the report itself.

use: DoCmd.OutputTo acOutputTable
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
jurictaAuthor Commented:
SimonAdept
The requirement by the office is it MUST BE TEXT file or it gets rejected.  I will try to reattach the CORRECTED files.  They were only adjusted to remove the line spaces and line up the column entries.  There can be no commas or any other markings on the files for the same reasons listed above.

CORRECTED Inventory should look like this (NO empthy lines):
  14120919041009OLF00021.FIL  00
  0014120919041009OLF00063      00
  631412091904100900700015141209
  631412091904100900800030141209
  631412091904100901100005141209
  631412091904100900109200006141209
  631412091904100900129200000141209
  631412091904100900130200003141209
  631412091904100900131200000141209
  631412091904100900131800000141209

CORRECTED Sales should look like this (ALL columns lined up):

fabriciofonseca:  I will try the tableooutput but like I said...it must be in text format
DailyInv141209---Corrected.txt
Sales141209---Corrected.txt
0
 
fabriciofonsecaCommented:
You can also use the DoCmd.TransferText  in the table
0
 
jurictaAuthor Commented:
I have tried to use the OutPutTo: acOutputTable (see code line below)
I have tried to use the TransferText (see code line below)

        DoCmd.OutputTo acOutputTable, "tblSalesDailySalesExport", acFormatTXT, ("C:\Users\juricta\Microsoft\Access\Database Files\Working\LiquorReports\Bubba3.txt")

        DoCmd.TransferText acExportDelim, , "tblSalesDailySalesExport", "c:\Users\juricta\Microsoft\Access\Database Files\Working\LiquorReports\bubba31.txt"

The results are attached but still wrong format.  I need just the data...NO quotation marks, NO dashes between records, and NO "PIPE | marks"

An EXAMPLE for SALES---> 3214121815461009001525215468PremisesNO
0
 
jurictaAuthor Commented:
Great information!!  Taught me a few things I did not know.  Works perectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.