Query Help (Access 2002)

[APATTO03]

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

[MarkD]

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

[APATTO03]

Mark,

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

Thanks,
Drew

[MarkD]

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

[APATTO03]

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

[MarkD]

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.

[HansV]

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.

[MarkD]

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

[APATTO03]

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