• Query Help (Access 2002)

    Author
    Topic
    #401792

    I need to setup a function that would change any character in a certain field that contains any character from an alternate table. For instance, a field may contain the following:
    ABC DEF_GHI%_JKL
    I want to setup a function or multiple functions that would replace the space, underscore, and percent all with a dash (ABC-DEF-GHI–JKL).
    I currently have a seperate table with the criteria (space, underscore, etc) that I want to replace in an alternate table. I have tried to use both the INSTR function and the REPLACE function, but had multiple problems. One being that when I run the query I get duplicated lines and the other is that it won’t depict the space as a character.
    Can someone help?

    Thanks,
    Drew

    Viewing 1 reply thread
    Author
    Replies
    • #794367

      I’d use a function for this purpose. Example:

      Public Function ReplaceNonAlphaNumeric(ByRef Txt As String, _
      ByRef Char As String) As String
      ‘ Txt = text to be parsed
      ‘ Char = character that will replace any non-alphanumeric characters
      ‘ (to strip non-alphanumeric specify zero-length string for Char)
      ‘ Non-alphanumeric ASCII codes:
      ‘ 32-47
      ‘ 58-64
      ‘ 91-96
      ‘ 123-127

      Dim n As Long

      For n = 32 To 127
      Select Case n
      Case 32 To 47, 58 To 64, 91 To 96, 123 To 127
      If InStr(1, Txt, Chr$(n), vbBinaryCompare) > 0 And _
      Chr$(n) Char Then
      Txt = Replace(Txt, Chr$(n), Char, , , vbBinaryCompare)
      End If
      Case Else
      ‘ skip test
      End Select
      Next n

      ReplaceNonAlphaNumeric = Txt

      End Function

      This will replace any non-alphanumeric character with the specified replacement character. If you only want to replace certain symbols or characters, simply modify the Select Case statement. Example of use:

      ? ReplaceNonAlphaNumeric(“–ABCDE %^&3 6475__”,”-“)
      –ABCDE—–3–6475–

      By specifying zero-length string for replacement character, you can strip all non-alphanumerics:

      ? ReplaceNonAlphaNumeric(“–ABCDE %^&3 6475__”,””)
      ABCDE36475

      Sample sub to print out printable ASCII character codes (32 to 127) to Debug window:

      Public Sub TestCharacters()
      Dim n As Integer
      For n = 32 To 127
      Debug.Print n & ” ” & Chr$(n)
      Next n
      End Sub

      This may be useful when determining correct Select Case statement. If the text being parsed may have carriage return/line feed characters, modify function to test for Chr$(13), Chr$(10), etc. You could use a table to store characters to replace, but I think this approach is simpler.

      HTH

      • #794964

        Mark,

        I’m getting an error on line 19 and 20 of your code.
        Do you get the same?

        Thanks,
        Drew

        • #795009

          I did not get any errors when testing code. It’d be useful to know the error number/description, also would help to provide example(s) of the text being evaluated that generates error, and in what context the code is being run – from a query? If running from a query, and it is possible for the text field being evaluated to be Null, you need to use NZ function when using function. Example:

          SELECT ReplaceNonAlphaNumeric(Nz([Field1]),”*”) AS Expr1
          FROM Table1;

          In this example, if Field1 is Null, the function will simply return a zero-length string.

          HTH

          • #795033

            The error was a compile error.
            I removed the semicolon from line 20 and everything seemed to work right. Is this to be included in the code?

            Thanks,
            Drew

            • #795043

              There are no semicolons in the code I posted. Did you copy & paste code into module, or did you type it in? Recommend copy/paste to avoid possible errors due to typos, etc.

            • #795070

              Some browsers change “not equal to” (the characters without a space in between), “less than or equal to” (the characters and = without a space in between) to HTML code containing a semi-colon.

            • #795076

              Thanx, didn’t realize that – I tend to use only IE which seems to render these characters correctly (see attached).

            • #795077

              Thanx, didn’t realize that – I tend to use only IE which seems to render these characters correctly (see attached).

            • #795071

              Some browsers change “not equal to” (the characters without a space in between), “less than or equal to” (the characters and = without a space in between) to HTML code containing a semi-colon.

            • #795081

              That’s weird, this is what I had for line 20 in VB after I copied your code in…
              Chr$(n) & lt > Char Then
              I should of double checked the code you pasted. Sorry.

              I copied, repasted, and everything seems to work perfectly.
              Thanks for the help.
              Regards,
              Drew

            • #795082

              That’s weird, this is what I had for line 20 in VB after I copied your code in…
              Chr$(n) & lt > Char Then
              I should of double checked the code you pasted. Sorry.

              I copied, repasted, and everything seems to work perfectly.
              Thanks for the help.
              Regards,
              Drew

            • #795044

              There are no semicolons in the code I posted. Did you copy & paste code into module, or did you type it in? Recommend copy/paste to avoid possible errors due to typos, etc.

          • #795034

            The error was a compile error.
            I removed the semicolon from line 20 and everything seemed to work right. Is this to be included in the code?

            Thanks,
            Drew

        • #795010

          I did not get any errors when testing code. It’d be useful to know the error number/description, also would help to provide example(s) of the text being evaluated that generates error, and in what context the code is being run – from a query? If running from a query, and it is possible for the text field being evaluated to be Null, you need to use NZ function when using function. Example:

          SELECT ReplaceNonAlphaNumeric(Nz([Field1]),”*”) AS Expr1
          FROM Table1;

          In this example, if Field1 is Null, the function will simply return a zero-length string.

          HTH

      • #794965

        Mark,

        I’m getting an error on line 19 and 20 of your code.
        Do you get the same?

        Thanks,
        Drew

    • #794368

      I’d use a function for this purpose. Example:

      Public Function ReplaceNonAlphaNumeric(ByRef Txt As String, _
      ByRef Char As String) As String
      ‘ Txt = text to be parsed
      ‘ Char = character that will replace any non-alphanumeric characters
      ‘ (to strip non-alphanumeric specify zero-length string for Char)
      ‘ Non-alphanumeric ASCII codes:
      ‘ 32-47
      ‘ 58-64
      ‘ 91-96
      ‘ 123-127

      Dim n As Long

      For n = 32 To 127
      Select Case n
      Case 32 To 47, 58 To 64, 91 To 96, 123 To 127
      If InStr(1, Txt, Chr$(n), vbBinaryCompare) > 0 And _
      Chr$(n) Char Then
      Txt = Replace(Txt, Chr$(n), Char, , , vbBinaryCompare)
      End If
      Case Else
      ‘ skip test
      End Select
      Next n

      ReplaceNonAlphaNumeric = Txt

      End Function

      This will replace any non-alphanumeric character with the specified replacement character. If you only want to replace certain symbols or characters, simply modify the Select Case statement. Example of use:

      ? ReplaceNonAlphaNumeric(“–ABCDE %^&3 6475__”,”-“)
      –ABCDE—–3–6475–

      By specifying zero-length string for replacement character, you can strip all non-alphanumerics:

      ? ReplaceNonAlphaNumeric(“–ABCDE %^&3 6475__”,””)
      ABCDE36475

      Sample sub to print out printable ASCII character codes (32 to 127) to Debug window:

      Public Sub TestCharacters()
      Dim n As Integer
      For n = 32 To 127
      Debug.Print n & ” ” & Chr$(n)
      Next n
      End Sub

      This may be useful when determining correct Select Case statement. If the text being parsed may have carriage return/line feed characters, modify function to test for Chr$(13), Chr$(10), etc. You could use a table to store characters to replace, but I think this approach is simpler.

      HTH

    Viewing 1 reply thread
    Reply To: Query Help (Access 2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: