Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Help (Access 2002)

    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

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Query Help (Access 2002)

    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

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access 2002)

    Mark,

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

    Thanks,
    Drew

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Query Help (Access 2002)

    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

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access 2002)

    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

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Query Help (Access 2002)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Help (Access 2002)

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

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Query Help (Access 2002)

    Thanx, didn't realize that - I tend to use only IE which seems to render these characters correctly (see attached).
    Attached Images Attached Images

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access 2002)

    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

Posting Permissions

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