Remove/Replace Non-Alphanumeric Character(s) in Access

[avishek29]

How do I remove RemoveNon-Alphanumeric Character(s) access?

For e.g. I have a field with the following characters:

Field1
123455*44

I would like to get rid of the "*".

Can you help me with this?

Thanks,
Avi

 

[KenReay]

Hi

How about using Replace() function (see help for syntax). If you many special characters to remove you could use multiple Replaces, or perhaps a do loop containing a replace(), where you vary the character to be replaced. Other useful functions for you might be asc(), chr()

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[Remou]

You may wish to test each character using IsNumeric.

 

[ck1999]

1st of all I know this is not vba forum but if you wanted a vba solution you could use

Sub test()
   calling = help("123455*44")
    MsgBox calling
End Sub


Function help(stuff)
Dim Counter
Dim sNumeric
Dim vendofloop
sNumeric = stuff
vendofloop = Len(sNumeric)
Counter = 0
Do While Counter < vendofloop
  Counter = Counter + 1
  If IsNumeric(Mid(sNumeric, Counter, 1)) Then
    
  Else
    sNumeric = Left(sNumeric, Counter - 1) & Right(sNumeric, Len(sNumeric) - Counter)
    Counter = Counter - 1
    vendofloop = Len(sNumeric)
  End If
Loop

help = sNumeric
End Function

You should also be able to put this function in a module and then call if from other areas. If you need to return a number vs string you may need to use help = val(sNumeric) instead

ck1999