Results 1 to 11 of 11
  1. #1
    jozino is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2015
    Posts
    7

    Write #TextFile


    Hello,

    Is there any way to remove the quotation marks from one/some fields in the output file when using Write #TextFile to export data from MS Access to a text file?
    Thanks.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are referring to single quotes as in O'Hara? Then Replace function, as in
    Replace([FieldName],"'","")
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    jozino,

    Are you working with Write # and/or Print # vba commands to output a sequential file?
    If so, see the description info in the links above.
    If not, then please tell us more about your issue.

  4. #4
    jozino is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2015
    Posts
    7
    I am referring double quotes since I am using the Write VBA command (Write # statement inserts commas between items and quotation marks around strings as they are written to the file); I want one field to be just number without quotes (like 99.9 instead of "99.9")

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try the Print # as described in the link I gave in post #3.
    If that doesn't help, then post your code.

    Working from the example on the referenced page.

    Code:
      Sub test_Print()
        Open "TESTFILE" For Output As #1    ' Open file for output.
        Print #1, "This is a test"    ' Print text to file.
        Print #1,    ' Print blank line to file.
        Print #1, "Zone 1"; Tab; "Zone 2"  ' Print in two print zones.
        Print #1, "Hello"; " "; "World"   ' Separate strings with space.
        Print #1, Spc(5); "5 leading spaces "  ' Print five leading spaces.
        Print #1, Tab(10); "Hello"  ' Print word at column 10.
    
    ' Assign Boolean, Date, Null, Error  and Number values.
        Dim MyBool, MyDate, MyNull, MyError, MyNumber
        MyBool = False: MyDate = #2/12/1969#: MyNull = Null
        MyError = CVErr(32767): MyNumber = 47.37
        ' True, False, Null, and Error are translated using locale settings of
        ' your system. Date literals are written using standard short date
        ' format.
        Print #1, MyBool; " is a Boolean value"
        Print #1, MyDate; " is a date"
        Print #1, MyNull; " is a null value"
        Print #1, MyError; " is an error value"
        Print #1, MyNumber; " is a number value via Print"
        Write #1, MyNumber; " is a number value via Write"
        Close #1    ' Close file.
    End Sub
    Here is the result:

    Code:
    This is a test
    
    Zone 1        Zone 2
    Hello World
         5 leading spaces 
             Hello
    False is a Boolean value
    12-Feb-69  is a date
    Null is a null value
    Error 32767 is an error value
     47.37  is a number value via Print
    47.37," is a number value via Write"

  6. #6
    jozino is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2015
    Posts
    7
    Thank you for your post. After reading this:
    https://wellsr.com/vba/2016/excel/vb...int-statement/
    I decided to use Write function over Print function since my output file would be read by other program as csv file and it sounds more convenient to use Write (only catch is one of eight fields needs to be a number)

    My code
    Code:
    Private Sub Command16_Click()
    On Error GoTo doADOErr
    
    
    Dim myData As New ADODB.Recordset
    Dim conn As New ADODB.Connection
    Dim mySQL, COMM As String
    Dim TextFile, TID, PREV, ACCT, i As Integer
    Dim FilePath, MDT, REF, VETA As String
    
    
      FilePath = "C:\GLT\MyFile.txt"
        TextFile = FreeFile
        Open FilePath For Output As TextFile
         
         
        Set conn = CurrentProject.Connection
        mySQL = "SELECT * FROM TRNS WHERE TRANSID>=" & Forms![TRNS]![Text12].Value & " AND TRANSID<=" & Forms![TRNS]![Text14].Value & ""
    
    
        myData.Open mySQL, conn, adOpenForwardOnly, adLockReadOnly
        If Not myData.EOF Then
            With myData
                i = 1
                Do Until .EOF
                    TID = .Fields(0)
                    MDT = Right(Format(Year(.Fields(1)), YY) & Format(Month(.Fields(1)), MM) & Format(Day(.Fields(1)), DD), 6)
                    
                    If Len(.Fields(5)) > 1 Then
                        COMM = COMM & ", " & .Fields(5)
                    End If
                    
                    REF = Format(Right(Year(.Fields(1)), 2), YY) & "/" & Format(Month(.Fields(1)), MM) & "/" & Format(Day(.Fields(1)), DD)
                    
                    If (i > 1 And PREV <> .Fields(0)) Then
                        If Len(COMM) > 1 Then
                            Write #TextFile, "C", COMM
                        End If
                    COMM = ""
                    End If
                    
                    If PREV <> .Fields(0) Then
                    Write #TextFile, "H,", MDT, 0
                    End If
                    
                    Write #TextFile, "D", "   " & .Fields(2), "      ", "ARCL", REF, MDT, .Fields(3), .Fields(4)
    
    
                    PREV = TID
                    i = i + 1
                    .MoveNext
                
                Loop
                    If Len(COMM) > 1 Then
                       Write #TextFile, "C", COMM
                    End If
            End With
        End If
          Close TextFile
        myData.Close
        conn.Close
        MsgBox "Export finished."
    
    
    doADOExit:
        Exit Sub
    
    
    doADOErr:
        MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
        Resume doADOExit
            
    End Sub
    My current output:
    Code:
    "H,","181130",0
    "D","   105","      ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT","2200.75"
    "D","   315","      ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT","-2000"
    "D","   315","      ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT","-300.75"
    "D","   840","      ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT","100"
    "C",", F. TEBB, Y. SHOJI"
    "H,","181115",0
    "D","   105","      ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT","99.9"
    "D","   315","      ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT","-9.9"
    "D","   315","      ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT","-90"
    Desired output
    Code:
    "H,","181130",0
    "D","   105","      ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT",2200.75
    "D","   315","      ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT",-2000
    "D","   315","      ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT",-300.75
    "D","   840","      ","ARCL","18/11/30","181130","FIRSTDATA DEPOSIT",100
    "C",", F. TEBB, Y. SHOJI"
    "H,","181115",0
    "D","   105","      ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT",99.9
    "D","   315","      ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT",-9.9
    "D","   315","      ","ARCL","18/11/15","181115","FIRSTDATA DEPOSIT",-90
    Thanks again

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe not the cause but you realize every variable not explicitly typed on your lines with multiple declarations is a variant? E.g. TID, PREV, ACCT? This can cause unexpected results

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As micron says, Variables in Access will be of type Variant, unless you explicitly Dim them as specific datatypes.
    For example, your code:
    Code:
    Dim mySQL, COMM As String
    Dim TextFile, TID, PREV, ACCT, i As Integer
    will be expressed in Access as
    mySql--Variant
    COMM --string
    TextFile--Variant
    TID--Variant
    PREV--Variant
    ACCT--Variant
    i--Integer

    In Access vba you can say

    Dim COMM as String
    Dim TID as Integer
    OR
    Dim COMM as String, TID as Integer

    each variable must be explicitly DIMmed or else it will be understood to be a Variant.

    It would be helpful to readers if you could post your original/input data.

    Good luck.

  9. #9
    jozino is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2015
    Posts
    7
    It looks like it is working now; the biggest change was replacing Write with Print.
    It isn't the nicest code but it does what I need :-)
    Thanks for your input.

    Code:
    Private Sub Command16_Click()
    'On Error GoTo doADOErr
    
    
    Dim myData As New ADODB.Recordset
    Dim conn As New ADODB.Connection
    Dim mySQL As String
    Dim COMM As String
    Dim TextFile As Integer
    Dim TID As Integer
    Dim PREV As Integer
    Dim ACCT As Integer
    Dim i As Integer
    Dim FilePath As String
    Dim MDT As String
    Dim REF As String
    Dim VETA As String
    
    
      FilePath = "C:\MyFile.txt"
        TextFile = FreeFile
        Open FilePath For Output As TextFile
         
         
        Set conn = CurrentProject.Connection
        mySQL = "SELECT * FROM TRNS WHERE TRANSID>=" & Forms![TRNS]![Text12].Value & " AND TRANSID<=" & Forms![TRNS]![Text14].Value & ""
    
    
        myData.Open mySQL, conn, adOpenForwardOnly, adLockReadOnly
        If Not myData.EOF Then
            With myData
                i = 1
                Do Until .EOF
                    TID = .Fields(0)
                    MDT = Right(Format(Year(.Fields(1)), YY) & Format(Month(.Fields(1)), MM) & Format(Day(.Fields(1)), DD), 6)
                    
                    If Len(.Fields(5)) > 1 Then
                        COMM = COMM & ", " & .Fields(5)
                    End If
                    
                    REF = Format(Right(Year(.Fields(1)), 2), YY) & "/" & Format(Month(.Fields(1)), MM) & "/" & Format(Day(.Fields(1)), DD)
                    
                    If (i > 1 And PREV <> .Fields(0)) Then
                        If Len(COMM) > 1 Then
                            Write #TextFile, "C", COMM
                        End If
                    COMM = ""
                    End If
                    
                    If PREV <> .Fields(0) Then
                    Write #TextFile, "H,", MDT, 0
                    End If
                    
                    'Write #TextFile, "D", "   " & .Fields(2), "      ", "ARCL", REF, MDT, .Fields(3), .Fields(4)
                    VETA = Chr$(34) & "D" & """," & Chr$(34) & .Fields(2) & """,""""      """", ""ARCL"""",""" & Format(Right(Year(.Fields(1)), 2), YY) & "/" & Format(Month(.Fields(1)), MM) & "/" & Format(Day(.Fields(1)), DD) & """,""" & Right(Format(Year(.Fields(1)), YY) & Format(Month(.Fields(1)), MM) & Format(Day(.Fields(1)), DD), 6) & """,""" & .Fields(3) & """," & .Fields(4)
                    Print #TextFile, VETA
    
    
                    PREV = TID
                    i = i + 1
                    .MoveNext
                
                Loop
                    If Len(COMM) > 1 Then
                       Write #TextFile, "C", COMM
                    End If
            End With
        End If
          Close TextFile
        myData.Close
        conn.Close
        MsgBox "Export finished."
    
    
    doADOExit:
        Exit Sub
    
    
    doADOErr:
        MsgBox Err.Description, vbOKOnly, Err.Source & ":" & Err.Number
        Resume doADOExit
            
    End Sub

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are missing the hash mark (#) in the Open command
    Code:
      FilePath = "C:\MyFile.txt"
        TextFile = FreeFile
        Open FilePath For Output As TextFile   '<<-- should be #Textfile
    and in the close command
    Code:
         Close TextFile   '<<-- should be #Textfile
        myData.Close
        conn.Close
        MsgBox "Export finished."



    Example from HELP:
    Code:
    Dim MyIndex, FileNumber
    For MyIndex = 1 To 5    ' Loop 5 times.
        FileNumber = FreeFile    ' Get unused file
            ' number.
        Open "TEST" & MyIndex For Output As #FileNumber    ' Create file name.
        Write #FileNumber, "This is a sample."    ' Output text.
        Close #FileNumber    ' Close file.
    Next MyIndex

  11. #11
    jozino is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2015
    Posts
    7
    Thanks, I corrected it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Write Conflict.
    By Homegrownandy in forum Forms
    Replies: 5
    Last Post: 05-18-2017, 05:29 PM
  2. Search on TextFile on C:
    By gstylianou in forum Access
    Replies: 14
    Last Post: 02-27-2016, 03:08 AM
  3. writing to textfile with string in " "
    By pradeep.sands in forum Forms
    Replies: 4
    Last Post: 07-17-2013, 09:51 AM
  4. Exporting 3 queries to 3 textfile
    By Grooz13 in forum Import/Export Data
    Replies: 4
    Last Post: 10-05-2010, 09:15 AM
  5. Some Can Read; Some Can Write
    By cassidym in forum Security
    Replies: 3
    Last Post: 08-19-2010, 02:19 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums