[AccessD] tabs in memo fields

That is a NICE function!

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Wednesday, January 05, 2005 9:40 AM
To: 'Access Developers discussion and problem solving'; 'Jim Lawrence'
Subject: RE: [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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com