Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 18th, 2008, 07:07 PM
Authorized User
 
Join Date: Dec 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Extending the Replace Function

I'm trying to replace all non alphanumeric characters in a column using Microsoft Access expression builder. Basically, if it is not an A through a Z (not case sensitive) or a 0 through a 9, I want it gone. I also want spaces out. Rather than using "replace()" to switch one character at a time, I'd like to either enter an array of characters ($,!,@,%,^,&,*,(, etc.) that need to be replaced by "". Or do like a regex that tells it to just strip out the non-alphanumeric characters.

Any suggestions?

 
Old February 19th, 2008, 10:11 AM
Authorized User
 
Join Date: Dec 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

With some help from someone with much more VB experience than me, I have obtained the following code. How can I implement this functionality in Access using the expression builder?:

Public Function RemoveNonAlphaNumeric(stringValue as string) as string

Dim runningValue as string
Dim character as string

For idx = 0 to len(stringValue) step 1

character = mid(stringValue, idx, 1)

Select character
Case "A", "B"
runningValue = runningValue & character
Case else
End Select

Next idx

RemoveNonAlphaNumeric = runningValue
End Function

 
Old February 19th, 2008, 11:03 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is the expanded version of that function.

Code:
Public Function RemoveNonAlphaNumeric(stringValue as string) as string

Dim runningValue as string
Dim character as string

For idx = 0 to len(stringValue) step 1
    character = mid(stringValue, idx, 1)
    Select Case ASC(character)
        Case  48 to 57, 65 to 90, 97 to 122
        runningValue = runningValue & character
    Case else
    End Select
Next idx

RemoveNonAlphaNumeric = runningValue
End Function
48 to 57 handles numerics
65 to 90 handles UPPER CASE
97 to 122 handles lower case



Rand
 
Old February 19th, 2008, 11:16 AM
Authorized User
 
Join Date: Dec 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks so much. When I try to test the code I get an error at the character line: character = Mid(stringValue, idx, 1).

The error pop-up says the following: Run-time error '5': Invalid procedure call or argument.

The value that I'm passing to the function is ABCDefghabcesdfsfasdsdf using the code RemoveNonAlphaNumeric("ABCDefghabcesdfsfasdsdf").

Thanks,
Andrew

 
Old February 19th, 2008, 11:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You will get that error message if the value of idx is less than 0.



Rand
 
Old February 19th, 2008, 12:00 PM
Authorized User
 
Join Date: Dec 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the tip. I got it to work by changing idx = 0 to idx = 1. I tested the code on the string " abcdefghijklmnopqr stuvwxyz0123456789 "

It returned abcdefghijklmnopqrstuvwxyz0123456789 (no space at the beginning, end or middle).

This did it! Thanks so much for your help!

If anyone wants the final code, here it is:

[functional code]

Option Compare Database
Option Explicit

Public Function RemoveNonAlphaNumeric(stringValue As String) As String
Dim runningValue As String
Dim character As String
Dim idx As Integer

For idx = 1 To Len(stringValue) Step 1
character = Mid(stringValue, idx, 1)
Select Case Asc(character)
Case 48 To 57, 65 To 90, 97 To 122
runningValue = runningValue & character
Case Else
End Select
Next idx

RemoveNonAlphaNumeric = runningValue

End Function

[/functional code]

 
Old February 19th, 2008, 12:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm a little suspicious of those index ranges. You may need to test the function without leading or trailing spaces to make certain that all the characters get properly processed. Also, add some non-alphanumeric characters to make certain that they are properly rejected.



Rand
 
Old February 19th, 2008, 12:23 PM
Authorized User
 
Join Date: Dec 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the tip. That is a good point. I just tried this string:

RemoveNonAlphaNumeric("~`!@#$%^&*()-_=+[{]}|\':;?/>.<, abcdefghijklmnop qrstuv~`!@#$%^&*()-_=+[{]}|\':;?/>.<, wxyz0123456~`!@#$%^&*()-_=+[{]}|\':;?/>.<, 789 ")

It returned abcdefghijklmnopqrstuvwxyz0123456789.

I think that we have it.

Thanks again for all your help! I really appreciate it.
 
Old February 19th, 2008, 12:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You must do a test where the first and last characters in the test string are characters that you want. This is the only way to verify that the starting and ending point are properly set.

Test String = "abcdef"

Watch out for a missing "a" or "f". If either one is missing, the starting point or ending point will need to be adjusted.





Rand
 
Old February 19th, 2008, 12:39 PM
Authorized User
 
Join Date: Dec 2007
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It returned abcdef






Similar Threads
Thread Thread Starter Forum Replies Last Post
Coalesce and replace function gregalb SQL Server 2000 1 May 15th, 2008 07:59 PM
Replace Function Query rsm42 ASP.NET 1.0 and 1.1 Basics 4 August 4th, 2007 05:29 AM
replace function keyvanjan Classic ASP Basics 3 May 15th, 2006 12:57 AM
replace function Adam H-W Classic ASP Basics 3 September 20th, 2004 09:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.