Is there anyway to strip all characters except numeric out of a value? For example: 999/777-5555. I want to strip the / and the hyphen. The characters can vary so is there anyway to recognize just numeric?

You could write a custom function to do this using the text manipulation functions.

:)
ken

I'm sorry but could you be a little more specific? What text manipulation functions are you referring to? Is there a "numeric only" function? I know of the InStr, Left,Mid, and Right functions but those aren't going to get the job done.

You would need to write your own function using the Mid, Left or other text manipulation functions.

Something Like

Function CleanString(ByVal tmpSTR As String) As String
Dim i As Long

For i = 1 To Len(tmpSTR)
If IsNumeric(Mid$(tmpSTR, i, 1)) Then
CleanString = CleanString & Mid$(tmpSTR, i, 1)
End If
Next i

End Function

wow! that seems a bit complicated however, I'm going to try it. Will that code cycle thru all the records and where will the new value be?

wow! that seems a bit complicated however, I'm going to try it. Will that code cycle thru all the records and where will the new value be?


Sub Test
dim mySTRING as String

mySTRING = Cleanstring("999/777-5555")

debug.print mySTRING

end sub

It didn' t work for me. The field is in a table of 6000 records where the phone number is keyed in so many different ways. Ex:999/999-9999 or 999.999.9999 or 9999999 . I just need to get it to display nothing but the numeric characters. I was hoping there was an easier function to run that would pull out only the numeric characters. I tried the Val function but it returns the numbers up to the unrecognizable character.

Can you put the table in a seprate database and post it - I'll write you the function...

Ken

thanks for all your help. I'm usually a quick learner but this one has me stumped. I've attached the database. Thanks again.

I used DJkarl's code. Look at this and see if it makes sense how it works...

:)
ken

I used DJkarl's code. Look at this and see if it makes sense how it works...

:)
ken

I don't quite understand how you are passing the field's value to tmpStr. I understand the For Next statement and the IsNumeric statement. But how are you getting the data passed to the variable?

Look at post 6 here for another simple function and see if it makes more sense:

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

:)
Ken

Thanks so much for all your help. The function worked. I think I understand now how the variable is being passed the value.

You're welcome -

:)
ken