Text format problem

[belsha]

In an Access 97 DB, a form that contains a memo field is being used to allow a large body of text to be pasted in it from Word. The text is in Courier Font. When the text is pasted in, all quotation marks are pasted as solid bars. Tried Times New Roman and other fonts (TT) and same result. If you type the text in, however, the quotation marks show up fine. Any ideas?

 

[ByteMyzer]

That's because MS Word autocorrects standard ASCII quotes (ASCII value 34) to Left and Right quotes (ASCII values 147 and 148, respectively). Changing fonts will do you no good here.

When you copy the text from MS Word and paste it into the Form field, you are pasting the special quotes, and not the standard ASCII quotes. The special quotes will appear as the solid bars you describe.

When you type it in manually, however, you get the standard quotes.

You can always use the Replace function to fix the pasted text, however. Create a command button on your form, cmdFix, and add the following text to your form's Code Module:

Private Sub cmdFix_Click()

    Me.[b]Text0[/b] = Replace(Replace(Me.[b]Text0[/b], Chr(147), ""), Chr(148), "")
    ' NOTE: The above statement assumes that [b]Text0[/b] is the name
    ' of your memo field.  Change [b]Text0[/b] to the actual name of your
    ' memo field for the code to work.

End Sub

Now, when you paste the text from MS Word into your memo field, and this should fix all the quotes.


NOTE: Here is the Replace function, for Access 97 users:

Public Function Replace(sIn As String, sFind As String, _
      sReplace As String, Optional nStart As Long = 1, _
      Optional nCount As Long = -1) As String

    Dim nC As Long, nIndex As Long, nPos As Long, sOut As String
    nIndex = nStart
    sOut = sIn
    nPos = InStr(nIndex, sOut, sFind, vbBinaryCompare)
    If nPos = 0 Then GoTo EndFn:
    Do
        nC = nC + 1
        nIndex = nPos + Len(sReplace)
        sOut = Left(sOut, nPos - 1) & sReplace & _
           Mid(sOut, nPos + Len(sFind))
        If nCount <> -1 And nC >= nCount Then Exit Do
        nPos = InStr(nIndex, sOut, sFind, vbBinaryCompare)
    Loop While nPos > 0
EndFn:
    Replace = sOut
End Function

 

[belsha]

Glad someone has seen this problem. Tried the code but get a compile error - expected variable and it points to Optional in the Public Function Replace segment ???? Thanks!

 

[ByteMyzer]

There should not be any compile errors with the Replace function. However, I did mistype the statement for modifying the text field in the cmdFix sub. The line should read:

Me.Text0 = Replace(Replace(Me.Text0, Chr(147), """"), Chr(148), """")

 

[RichO]

I would have thought that this would replace the single quotes with double quotes.

Me.Text0 = Replace(Replace(Me.Text0, Chr(147), """"), Chr(148), """")

I was thinking:

Me.Text0 = Replace(Replace(Me.Text0, Chr(147), Chr$(34)), Chr(148), Chr$(34))

Both of these methods work the same, but I was wondering why the double quotes come out as only one in the replace function.

 

[ByteMyzer]

It has to do with the way Visual Basic interprets quotes in a string. For example:

? """" & "Hello" & """"
...returns:
"Hello"

? """Hello" & """"
...also returns:
"Hello"

? "Bob said, ""Take it."""
...returns:
Bob said, "Take it."