Problem with superfluous decimals during export to .txt

[Clueless Newbie]

Hi!

I'm currently using the following code in order to a) export certain fields from one table into a TAB-delimited .txt file and b) update a second table in the process:

Private Sub Datenexport_Click()
On Error Resume Next

    Dim db1 As Database
    
    Dim rs As Recordset      'DruckReihenfolge
    Dim f As Form
    Dim I As Integer
    Dim strKriterien As String
    Dim SQery As String
    Dim Anzahl As Integer
    Dim listeZuVergeben As String
    
    Set db1 = CurrentDb 'Datenbank öffnen.
    On Error Resume Next
    
    Dim ctlQuelle As Control
    Dim strElemente As Variant
    Dim intAktuelleZeile As Integer
    Set ctlQuelle = Forms!Syssuche!Listbox1
    Dim satz As Recordset
    Dim satznummer As Recordset
     
    
    For Each strElemente In ctlQuelle.ItemsSelected
        listeZuVergeben = listeZuVergeben & " , " & ctlQuelle.Column(0, strElemente)
    Next
            
    CurrentDb.QueryDefs("Export1").sql = "SELECT Vertragsnummern.VertragsNr, Daten.Kunde, Daten.Vertragsinhalt, Daten.Beginn, Daten.Ende, daten.angebotnr, Daten.DateinamePDF, daten.id FROM Daten LEFT JOIN Vertragsnummern ON Daten.ID = Vertragsnummern.ID WHERE Vertragsnummern.in_exportliste <> True and Daten.id in (" & Mid(listeZuVergeben, 3) & ")"
     
    DoCmd.TransferText acExportDelim, "ExpBeschr", "Export1", "C:\FileNet_" & Format(Date, "yyyymmdd") & ".txt"
    
    MsgBox ("Die ausgewählten Datensätze wurden in einer Textdatei im Verzeichnis C:\ gespeichert")
    DoCmd.OpenReport "Datenexport", acViewPreview, , , , Mid(listeZuVergeben, 3)
    
    Dim gemerkt As Integer
    germekt = 0
    Set satz = db1.OpenRecordset("export1", dbOpenDynaset)
    If Not satz.EOF Then
    Do While Not satz.EOF
        If gemerkt <> satz!ID Then
        Set satznummer = db1.OpenRecordset("select * from Vertragsnummern where id = " & satz!ID)
            Do While Not satznummer.EOF
                satznummer.Edit
                satznummer!In_Exportliste = True
                satznummer.Update
            satznummer.MoveNext
            Loop
        End If
        gemerkt = satz!ID
        satz.MoveNext
    Loop
    End If
    
    
    
    MsgBox ("Die ausgewählten Datensätze wurden in einer Textdatei im Verzeichnis C:\ gespeichert")
    
    DoCmd.OpenReport "Datenexport", acViewPreview, , , , Mid(listeZuVergeben, 3)
    
End Sub

It works fine - except for one minor snag: the values of the first exported field (numeric value) look OK in Access's tables, reports and queries, but they are exported to .txt with two decimals. However, these values are in fact contract numbers and should have neither commas nor decimals at all. Having experimented with all sorts of numeric properties I even considered defining the field in question as a text field, but of course the customer wouldn't really appreciate the effect this would have on the sort-by option. How can I suppress the comma and the decimals or cut those characters off in my text file? Any hint would be greatly appreciated.

Regards & thanks,

Ute

 

[Clueless Newbie]

OK, got it.

      Dim s As String

      Open "C:\FileNet_" & Format(Date, "yyyymmdd") & ".txt" For Binary As #1
      s = Input(LOF(1), 1)
      Close #1
      s = Replace$(s, ",00", "")
        
      Open "C:\FileNet_" & Format(Date, "yyyymmdd") & ".txt" For Output As #1
      Print #1, s
      Close #1