Every third row blank in exported text file (1 Viewer)

[sphere_monk]

HI Everyone,

I have a database where I need to export data to text files and import that data into various other software. To do that, I have been creating reports with no report or page headers and only one text field in the "Detail" section of the report. The report has a "Record Source" of the following:

SELECT tbl_GunExport.PropertyRouteBook, tbl_GunExport.PropertySortOrder, tbl_GunExport.Account, tbl_GunExport.Name AS NameAlt, tbl_GunExport.[Meter Tag], tbl_GunExport.Address, tbl_GunExport.[Reading Message], tbl_GunExport.[Previous Reading] FROM tbl_GunExport ORDER BY tbl_GunExport.PropertyRouteBook, tbl_GunExport.PropertySortOrder;

and the text field is unbound, with a "Control Source" of the following:

=[Account] & "|" & [NameAlt] & "|" & [Meter Tag] & "|" & [Address] & "|" & [Reading Message] & "|" & [Previous Reading]

To launch the report and export the records to a text file, I have a form that runs the following code when a button is clicked:

Private Sub cmdExport_Click()
On Error GoTo Err_cmdExport_Click

If IsNull(Forms!frmExportToGun!cboCycle) Then
    MsgBox ("You must enter a Cycle to Process")
    GoTo Exit_cmdExport_Click
Else
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryGunExportDelete"
    DoCmd.OpenQuery "qryGunExport"
    DoCmd.OutputTo acReport, "rptGunExport", "MS-DOSText(*.txt)", "U:\Export\" & Forms!frmExportToGun!cboCycle & "-ExpToGun.txt", False, "", 0
    MsgBox ("File " & Forms!frmExportToGun!cboCycle & "-ExpToGun Exported")
    DoCmd.Close acForm, "frmExportToGun"
End If

Exit_cmdExport_Click:
    DoCmd.SetWarnings True
    Exit Sub
    
Err_cmdExport_Click:
    MsgBox Err.Description
    Resume Exit_cmdExport_Click

End Sub

The problem I'm having is that every third record in the resulting text file is a blank record, as in the following example:

01003000000|Jeanne Levesque|00281389|11 Bridge St|R/S|1200
01000000000|Leo Enrico|55530182|13 Bridge St|R/S of 11 Bridge|200

01312000000|Patricia Maloney|60928876|31 Bridge St|R/S|
01004000000|Norman Leblanc|55442075|11 Bridge St  Upstairs|R/S|100
01006000000|Kristen Tufnell|60963110|9 Bridge St|R/S|

01012000000|William Holloway Sr|00123635|7 Bridge St|B/H R/S|1309
01015000001|Marcia Bernstein|59043845|5 Bridge St|L/S|
01018000001|Thomas Gilson|54060297|6 Twin Pines Apt 1 & 2|R/S|175

01021000001|Thomas Gilson|54640499|6 Twin Pines|BH GA Windowsill|100
01024000000|William Morin|57193488|6 Front St|L/S CMP|
01027000005|Andrea White|60701823|7 Front St|L/S CMP|

01030000002|Amanda L Russell|00433109|7 Front St Apt A|B/H R/S|775
01033000000|Patrick O Regan|53549502|9 Front St|R/S|175
01036000004|Gina Davies|00383778|8 Front St|R/S|525

There are no blank records in the table, and if you print preview the report it looks fine. Making the size of the text field larger does not change the interval of the blank records. Increasing the size of the "Detail" section only decreases the number of records that appear between the blank sections as in the following example:

01003000000|Jeanne Levesque|00281389|11 Bridge St|R/S|1200



01000000000|Leo Enrico|55530182|13 Bridge St|R/S of 11 Bridge|200


01312000000|Patricia Maloney|60928876|31 Bridge St|R/S|



01004000000|Norman Leblanc|55442075|11 Bridge St  Upstairs|R/S|100



01006000000|Kristen Tufnell|60963110|9 Bridge St|R/S|


01012000000|William Holloway Sr|00123635|7 Bridge St|B/H R/S|1309



01015000001|Marcia Bernstein|59043845|5 Bridge St|L/S|



01018000001|Thomas Gilson|54060297|6 Twin Pines Apt 1 & 2|R/S|175


01021000001|Thomas Gilson|54640499|6 Twin Pines|BH GA Windowsill|100

As you can see, the spaces are still inconsistent in the above example, so the intervening blank row is still in there. If it wasn't, the spaces would be equal.

Does anyone know what might be causing this? Any help would be greatly appreciated.

Thanks,

 

[DCrake]

Simple Software Solutions

If you attempt this in code as opposed to doing it via a report you would get over this issue.

First create a recordset based on the data you are exporting

Then use the Open File for Output functionality to create the text file

Then loop through you recordset
use the command

Print #1 ,RS("Account") & "|" & RS("NameAlt") & "|" & RS("Meter Tag") & "|" & RS("Address") & "|" & RS("Reading Message") & "|" & RS("Previous Reading")

at the end of the recordset use the #Close to save the text file.


Something on the lines of

Dim RS as DAO.Recordset

Set RS = CurrentDB.OpenRecordset("YourQuery")

If Not RS.EOF  Then

   Open "C:\Export\Exports.Txt" For Output As #1
   Do Until Rs.EOF
     Print #1 ,RS("Account") & "|" & RS("NameAlt") & "|" & RS("Meter Tag") & "|" & RS("Address") & "|" & RS("Reading Message") & "|" & RS("Previous Reading")
    RS.MoveNext
   Loop

   RS.Close
   Close #1
End If
Set Rs = Nothing

Aircode not tested

CodeMaster:

 

[sphere_monk]

Great! I'll change from exporting using the report to exporting using the code you gave me. Thanks!!!

 

[DCrake]

Don't forget to let us know how you got on and if the sample code worked or not. Feedback is good for other readers as well as the poster.