CODEWITHSUNDEEP
ms-access
Jacques Tardie
Jacques Tardie

Reputation: 595

Removing non-alphanumeric characters in an Access Field

I need to remove hyphens from a string in a large number of access fields. What's the best way to go about doing this?

Currently, the entries are follow this general format:

  2010-54-1
  2010-56-1
  etc.

I'm trying to run append queries off of this field, but I'm always getting validation errors causing the query to fail. I think the cause of this failure is the hypens in the entries, which is why I need to remove them.

I've googled, and I see that there are a number of formatting guides using vbscript, but I'm not sure how I can integrate vb into Access. It's new to me :)

Thanks in advance, Jacques

EDIT:

So, Ive run a test case with some values that are simply text. They don't work either, the issue isn't the hyphens.

Upvotes: 0

Views: 7449

Answers (2)

MarkII
MarkII

Reputation: 892

I have a function I use when removing everything except Alphanumeric characters. Simply create a query and use the function in the query on whatever field you are trying to modify. Runs much faster than find and replace.

Public Function AlphaNumeric(inputStr As String)
    Dim ascVal As Integer, originalStr As String, newStr As String, counter As Integer, trimStr As String
    On Error GoTo Err_Stuff
    ' send to error message handler
    If inputStr = "" Then Exit Function
    ' if nothing there quit
    trimStr = Trim(inputStr)
    ' trim out spaces
    newStr = ""
    ' initiate string to return
    For counter = 1 To Len(trimStr)
        ' iterate over length of string
        ascVal = Asc(Mid$(trimStr, counter, 1))
        ' find ascii vale of string
        Select Case ascVal
            Case 48 To 57, 65 To 90, 97 To 122
                ' if value in case then acceptable to keep
                newStr = newStr & Chr(ascVal)
                ' add new value to existing new string
        End Select
    Next counter
    ' move to next character
    AlphaNumeric = newStr
    ' return new completed string
Exit Function
Err_Stuff:
    ' handler for errors
    MsgBox Err.Number & " " & Err.Description
End Function

Just noticed the link to the code, looks similar to mine. Guess this is just another option.

Upvotes: 1

AlexCuse
AlexCuse

Reputation: 18296

I'm not sure that the hyphens are actually the problem without seeing sample data / query but if all you need to do is get rid of them, the Replace function should be sufficient (you can use this in the query)

example: http://www.techonthenet.com/access/functions/string/replace.php

If you need to do some more advanced string manipulation than this (or multiple calls to replace) you might want to create a VBA function you can call from your query, like this:

http://www.pcreview.co.uk/forums/thread-2596934.php

To do this you'd just need to add a module to your access project, and add the function there to be able to use it in your query.

Upvotes: 1

Related Questions