[AccessD] tabs in memo fields

Hale, Jim Jim.Hale at FleetPride.com
Wed Jan 5 10:18:16 CST 2005


Anybody know of a similar function that could be used to validate file
names? I let users add short descriptions to file names before they are
saved and I badly need a function to validate their input.
Jim Hale

-----Original Message-----
From: John W. Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Wednesday, January 05, 2005 9:50 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [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



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.


More information about the AccessD mailing list