Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Need to clean, then omit characters

    I have a database up and running and I need to refine it, I have 4,500 rows of contact names in a table that were imported from excel.

    What is the easiest way to remove special characters from my contact names, which is field name ACCOUNT? An update query?
    The characters we need to remove are: ".", ",", leading text "The", and "-"
    I can't figure out the difference between Replace and remove in the SQL examples I've seen.

    Once that is done, I am looking for a way to prevent those from being saved in new data in the data entry form . I have been looking at Dim illegalChars, stripspecialchar, clean (functions) but am not exactly sure how to run this type of code (without event procedure is new to me).

    Any help would be greatly appreciated!

  2. #2
    Join Date
    May 2010
    Location
    Sydney Australia
    Posts
    4
    With Replace "" eliminates and " " replaces with a space. It would also help you to post some actual data examples as combinatons of Right, Left, Mid, InStr, InStrRev and Len functions might get the job done. Sometimes the easiest way is to do it in stages, that is, make a calculated field in a query that gives a result from your field with the data and then create another field that uses the extracted data in the first created field and so on down the line.

    Leading text "The" could be removed with the Right function, that is, only stuff to the right of the "The" will be extracted

    Here is a function that might do what you need. I have set this so as to remove + - . ,

    Thus abc,d"-kk,,z will appear as abcdkkz

    ab c , d " - k k , , z will appear as ab c d k k z

    Public Function XxNum2Forum(strOriginalString As String) As String
    Dim lngCtr As Long
    Dim lngLength As Long
    Dim strTheCharacter As String
    Dim intAscii As Integer
    Dim strFixed As String
    lngLength = Len(strOriginalString)
    For lngCtr = 1 To lngLength
    strTheCharacter = Mid(strOriginalString, lngCtr, 1)
    intAscii = Asc(strTheCharacter)
    If (intAscii > 0 And intAscii < 34) Or intAscii > 46 Then
    strFixed = strFixed & strTheCharacter
    End If
    Next lngCtr
    XxNum2Forum = strFixed
    End Function

  3. #3
    Join Date
    May 2010
    Location
    Sydney Australia
    Posts
    4
    I fogot to include this function. It does the same as above but will drop any spaces and so

    12. - 3b" bZZz-- zx will appear as 123bbZZzzx

    Public Function LCUCNu(strOriginalString As String) As String
    Dim lngCtr As Long
    Dim lngLength As Long
    Dim strTheCharacter As String
    Dim intAscii As Integer
    Dim strFixed As String

    lngLength = Len(strOriginalString)
    For lngCtr = 1 To lngLength
    strTheCharacter = Mid(strOriginalString, lngCtr, 1)
    intAscii = Asc(strTheCharacter)
    If intAscii < 91 And intAscii > 64 Or intAscii >= 97 And intAscii <= 122 Or intAscii >= 48 And intAscii <= 57 Then
    strFixed = strFixed & strTheCharacter
    End If
    Next lngCtr
    LCUCNu = strFixed

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •