[AccessD] tabs in memo fields

I use this function to get rid of Tabs and other pesky characters in memo
fields. I usually run it in the LostFocus event of the textboxes where memos
are edited, but it should work just fine in a query too.

Function ConvertToPlainAscii(sString As String) As String
' Converts TAB characters to spaces and curly quote characters to standard
quote characters.
' some users may paste such data into memo fields and this causes problems
with reports
Const TABCHAR = 9
Const LEFTSINGLEQUOTE = 145
Const RIGHTSINGLEQUOTE = 146
Const LEFTDOUBLEQUOTE = 147
Const RIGHTDOUBLEQUOTE = 148
Const TABSPACES = "    "
    If Nz(sString) > "" Then
        While InStr(sString, Chr(TABCHAR)) > 0
            sString = ReplaceString(sString, Chr(TABCHAR), TABSPACES)
        Wend
        While InStr(sString, Chr(LEFTSINGLEQUOTE)) > 0
            sString = ReplaceString(sString, Chr(LEFTSINGLEQUOTE), "'")
        Wend
        While InStr(sString, Chr(RIGHTSINGLEQUOTE)) > 0
            sString = ReplaceString(sString, Chr(RIGHTSINGLEQUOTE), "'")
        Wend
        While InStr(sString, Chr(LEFTDOUBLEQUOTE)) > 0
            sString = ReplaceString(sString, Chr(LEFTDOUBLEQUOTE), """")
        Wend
        While InStr(sString, Chr(RIGHTDOUBLEQUOTE)) > 0
            sString = ReplaceString(sString, Chr(RIGHTDOUBLEQUOTE), """")
        Wend
    End If
    ConvertToPlainAscii = Nz(sString, "")
End Function

and this is the ReplaceString function it uses..

Function ReplaceString(strData As String, strFind As String, strReplace As
String) As String
  '============================================================
  '     Purpose: Replaces the first occurrence of strFind in strData with
strReplace
  '============================================================
Dim strTemp1 As String, strTemp2 As String

    On Error GoTo strReplace_Err
    If InStr(strData, strFind) > 0 Then
    strTemp1 = Left(strData, InStr(strData, strFind) - 1)
        strTemp2 = Mid(strData, InStr(strData, strFind) + Len(strFind))
        strData = strTemp1 & strReplace & strTemp2
    End If
strReplace_Exit:
    ReplaceString = strData
    Exit Function

strReplace_Err:
    Select Case Err
        Case Else
 		' replace with a call to your favorite error reporting
routine
            ReportError Err.Number, Err.Description, "ReplaceString",
"Module : String handling Tools"
            Resume strReplace_Exit
    End Select
End Function

HTH

Lambert


> -----Original Message-----
> From:	accessd-bounces at databaseadvisors.com
> [SMTP:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
> Sent:	Wednesday, January 05, 2005 9:20 AM
> To:	'Access Developers discussion and problem solving'
> Subject:	RE: [AccessD] tabs in memo fields
> 
> Hi Roz:
> 
> Thanks for your help. I have been playing with tracking the tabs (chr(9))
> with 'instr' but have been having some issues handling the fields through
> a
> function as the memo content is so large. 
> 
> Thanks again for your help.
> Jim
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Roz Clarke
> Sent: Wednesday, January 05, 2005 1:23 AM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] tabs in memo fields
> 
> Hi Jim
> 
> I hope some bright spark who actually slept last night will come up with
> an
> elegant solution because the only thing I can come up with at the moment
> is
> rather clunky.
> 
> Do an INSTR to find the first tab character (CHR(9) iirc). Keep doing this
> on the same memo until you don't find a tab, then move on to the mext
> memo.
> You could use a flag to indicate whether a tab was found on the last pass
> or
> not, so that your code knows whether to look at the same memo again or
> move
> on to the next one.
> 
> Untested, ungraceful... if there's a neat way of doing it I'd like to know
> too
> 
> Roz
> 
> -----Original Message-----
> From: Jim Lawrence [mailto:accessd at shaw.ca] 
> Sent: 05 January 2005 07:52
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] tabs in memo fields
> 
> 
> Hi All:
> 
> It seems simple but, is there a way to replace tab characters within a
> memo
> field using a query or function? Note, memo field can be many hundreds of
> characters.
> 
> TIA
> Jim
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com