Getting rid of punctuation in table fields

[catbeasy]

Hi

Wanted to know if anyone could provide some code that would go through a table and get rid of punctuation in a field? I'm looking to get rid of periods, apostrophes and commas..

Thanks for any assistance..!

 

[MSAccessRookie]

Hi

Wanted to know if anyone could provide some code that would go through a table and get rid of punctuation in a field? I'm looking to get rid of periods, apostrophes and commas..

Thanks for any assistance..!

The Access Built-In Replace() Function might be of help to you. Look it up and get back if you have any questions.

 

[catbeasy]

The Access Built-In Replace() Function might be of help to you. Look it up and get back if you have any questions.

I should have mentioned that I'm using Access 97, which apparently doesn't have the Replace function..any suggestions on how to proceed without this?

Thanks..

 

[MSAccessRookie]

I should have mentioned that I'm using Access 97, which apparently doesn't have the Replace function..any suggestions on how to proceed without this?

Thanks..

If Replace() is unavailable, then you can build a function that takes a Source String as a parameter, and uses the following functions:

• Instr() to locate the Characters that you want to remove
• Left() to get all characters BEFORE any character that you want to remove.
• Right() to get all characters AFTER any character that you want to remove.

Have the Function use a While Loop that locates and removes target characters one at a time, and exits whenever the Source String no longer contains any of the target characters.

 

[catbeasy]

If Replace() is unavailable, then you can build a function that takes a Source String as a parameter, and uses the following functions:

• Instr() to locate the Characters that you want to remove
• Left() to get all characters BEFORE any character that you want to remove.
• Right() to get all characters AFTER any character that you want to remove.

Have the Function use a While Loop that locates and removes target characters one at a time, and exits whenever the Source String no longer contains any of the target characters.

ok, thanks, I'll give it a shot..

 

[Atomic Shrimp]

A long time ago (in a galaxy far away), I wrote a general purpose function to filter unwanted characters out of a string. Here it is:

Public Function AllowOnly(yourstring As String, Allowed As String)
If Allowed = "" Then Allowed = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim intLoop As Integer
For intLoop = 1 To Len(yourstring)
    If InStr(Allowed, Mid(yourstring, intLoop, 1)) > 0 Then
        AllowOnly = AllowOnly & Mid(yourstring, intLoop, 1)
    End If
Next intLoop

End Function

So
AllowOnly("1234abc","1234567890") returns "1234"

AllowOnly("123abc456","1234567890") returns "123456"

AllowOnly("123&%$@abc","") returns "123abc" (if you don't tell it what is allowed, it defaults to alphanumeric, but you can change the default by amending it in the function code)

It can be used in an update query something like this:
UPDATE MyTable SET MyTable.FieldName = allowonly([FieldName],"{the list of permitted characters here}");

 

[HiTechCoach]

For functions that were added to Access 2000 and later, [place the following code in a code module:

Name the module: mod2kFunctions

'Split         Split a string into a variant array.
'
'InStrRev      Similar to InStr but searches from end of string.
'
'Replace       To find a particular string and replace it.
'
'Reverse       To reverse a string.

Public Function InStrRev(ByVal sIn As String, ByVal _
   sFind As String, Optional nStart As Long = 1, _
    Optional bCompare As VbCompareMethod = vbBinaryCompare) _
    As Long

    Dim nPos As Long
    
    sIn = Reverse(sIn)
    sFind = Reverse(sFind)
    
    nPos = InStr(nStart, sIn, sFind, bCompare)
    If nPos = 0 Then
        InStrRev = 0
    Else
        InStrRev = Len(sIn) - nPos - Len(sFind) + 2
    End If
End Function

Public Function Join(Source() As String, _
    Optional sDelim As String = " ") As String

    Dim nC As Long
    Dim sOut As String
    
    For nC = LBound(Source) To UBound(Source) - 1
        sOut = sOut & Source(nC) & sDelim
    Next
    
    Join = sOut & Source(nC)
End Function

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

    Dim nC As Long, nPos As Long
    Dim nFindLen As Long, nReplaceLen As Long

    nFindLen = Len(sFind)
    nReplaceLen = Len(sReplace)
    
    If (sFind <> "") And (sFind <> sReplace) Then
        nPos = InStr(nStart, sIn, sFind, bCompare)
        Do While nPos
            nC = nC + 1
            sIn = Left(sIn, nPos - 1) & sReplace & _
             Mid(sIn, nPos + nFindLen)
            If nCount <> -1 And nC >= nCount Then Exit Do
            nPos = InStr(nPos + nReplaceLen, sIn, sFind, _
              bCompare)
        Loop
    End If

    Replace = sIn
End Function

Public Function Reverse(ByVal sIn As String) As String
    Dim nC As Long
    Dim sOut As String

    For nC = Len(sIn) To 1 Step -1
        sOut = sOut & Mid(sIn, nC, 1)
    Next nC
    
    Reverse = sOut
End Function

Public Function Split(ByVal sIn As String, _
    Optional sDelim As String = " ", _
    Optional nLimit As Long = -1, _
    Optional bCompare As VbCompareMethod = vbBinaryCompare) _
    As Variant

    Dim nC As Long, nPos As Long, nDelimLen As Long
    Dim sOut() As String
    
    If sDelim <> "" Then
        nDelimLen = Len(sDelim)
        nPos = InStr(1, sIn, sDelim, bCompare)
        Do While nPos
            ReDim Preserve sOut(nC)
            sOut(nC) = Left(sIn, nPos - 1)
            sIn = Mid(sIn, nPos + nDelimLen)
            nC = nC + 1
            If nLimit <> -1 And nC >= nLimit Then Exit Do
            nPos = InStr(1, sIn, sDelim, bCompare)
        Loop
    End If

    ReDim Preserve sOut(nC)
    sOut(nC) = sIn

    Split = sOut
End Function