Removing Spaces in a text field

[mous]

Hello

I have a field which contains a telephone number. I need to remove all the spaces from this field so I am just left with numbers. Ltrim and Rtrim won't work as I want the middle spaces removed not trailing.

This function is required in a query and I'm using Access 97.

For example, 0121 457 8963 will become 01214578963.

Many Thanks

Dawn

 

[antomack]

Check the following:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=33193&highlight=replace

 

[raskew]

Hi -

Here's another function you may want to check out. It will remove specified characters, as well as spaces.

Function despace(ByVal pstr As String, pItem As String) As String
'*******************************************
'Purpose:    Removes characters from a string
'Coded by:  raskew
'Inputs:     ? despace("xx:xx:xx:xx:xx", ":")
'Output:    "xxxxxxxxxx"
'*******************************************

Dim strHold As String

    strHold = RTrim(pstr)
    Do While InStr(strHold, pItem) > 0
      strHold = Left(strHold, InStr(strHold, pItem) - 1) & Mid(strHold, InStr(strHold, pItem) + 1)
    Loop
    despace = strHold
End Function

HTH - Bob

 

[vbaInet]

We could just use:

Mid(Val([COLOR=Red]"1"[/COLOR] & "0121 457 8963"), 2)

Ensure the "1" remains.

Or use the Replace() function.

 

[vbaInet]

Oh, just realised this is an old post. I guess raskew was just providing a solution