Enter Key Wreaks Havoc when Exporting Databases to Text Files

This value gets passed between the database and the application effortlessly. The problem arises when inevitably you want to export the tables into a flat file. It exports just fine except that the Chr$(13) & Chr$(10) get converted back to the effects of an enter keystroke and cause your export file to be out of whack. Then, when you want to import the file into the new (or old) tables, you get import errors galore and the import process halt’s.

The work around is not pretty, but it is effective. This is a two stage process with the first stage looking similar to the above tip. First, we have to convert all the Chr$(13) & Chr$(10)’s into something that DOS won’t treat any different than a regular character. We cannot use a keyboard character as our token as inevitably, no matter how obscure you make the symbol, someone will type it in. I chose Chr$(6) which is nothing in particular except an unprintable character.

MySet("Comments") = ReplaceEnter(txtComments.Text)

Function ReplaceEnter (ByVal ParseText As String) As String

Dim Offset as Integer, x as integer

    ParseText = LTrim$(Trim$(ParseText))

    x = InStr(ParseText, Chr$(13))

    If x = 0 Then

        ReplaceEnter = ParseText & " " ' Takes care of the Access zero length string problem

        Exit Function

    End If

    Offset = 1

    Do While x > 0

        ParseText = Left$(ParseText, x - 1) & Chr$(6) & Mid$(ParseText, x + 2)

        Offset = x + 1

        x = InStr(Offset, ParseText, Chr$(13))

    Loop

    ReplaceEnter = ParseText

End Function

So, we do the update on the database but there comes a time when you want to redisplay the contents of the field. Now, you have to reverse the process and convert the Chr$(6)’s into Chr$(13) & Chr$(10)’s:

txtComments.Text = ReplaceChr6(MySet("Comments") & "")

Function ReplaceChr6 (ByVal ParseText As String) As String

Dim Offset as Integer, x as integer

    x = InStr(ParseText, Chr$(6))

    If x = 0 Then

        ReplaceChr6 = ParseText

        Exit Function

    End If

    Offset = 1

    Do While x > 0

        ParseText = Left$(ParseText, x - 1) & Chr$(13) & Chr$(10) & Mid$(ParseText, x + 1)

        Offset = x

        x = InStr(Offset, ParseText, Chr$(6))

    Loop

    ReplaceChr6 = ParseText

End Function

As I said at the beginning, it isn’t pretty, but it does get you around a problem you may not be aware of until your user decides to export the file so he/ she can do something with it.

 

Tip Submitted By: Paul A. Birkbeck