By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,580 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

Deleting Spaces between numbers or text in a field

P: n/a
Help please, I want to write a query to delete spaces and other various
between numbers (which is set as text field due to the data that is put
in) as follows:

Currently as:
0-9-123 4567
OR
0-9-765-4321

Result I want:
1234567
7654321

Is there a way?? have tried a trim query - but doesn't work. Your kind
feedback would be most appreciated.

Zheve

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
One approach would be to use the Replace function together with the Right
function. Based on your example, something like the following might work:

Replace(Replace(Right("0-9-765-4321",8)," ",""), "-", "")

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Zheve" <ma*****@xtra.co.nz> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Help please, I want to write a query to delete spaces and other various
between numbers (which is set as text field due to the data that is put
in) as follows:

Currently as:
0-9-123 4567
OR
0-9-765-4321

Result I want:
1234567
7654321

Is there a way?? have tried a trim query - but doesn't work. Your kind
feedback would be most appreciated.

Zheve
Nov 13 '05 #2

P: n/a
Thank you for your help, however as I am fairly new to Access I have
tried your sugested example and various others etc. But they all come
up with "Undefined function 'Replace' in expression." This is what I
have tried as a select query.
Field is Telephone

1. As your suggestion above
2.Replace (Telephone(Right("0-9-765-4321",8)," ",""), "-", "")
3.Telephone(Replace(Right("0-9-765-4321",8)," ",""), "-", "")

Also as there are around 500,000 phone numbers in this field with the
above format do I replace 0-9-765-4321 with 0-9-###-####?

Nov 13 '05 #3

P: n/a
Are you using A97, which would explain the undefined function message.

Try copying/pasting this to a new module and calling from the debug
window with:
? chopit(" 0-9-765-4321 ", "0-9", "-", " ")
7654321

HTH - Bob

Function ChopIt(pStr As String, ParamArray varmyvals() As Variant) As
String
'*******************************************
'Purpose: Remove a list of unwanted
' characters from a string
'Coded by: raskew
'Inputs: From debug window:
' '? chopit("(626) 123-5555", ")","(", "-")
'Output: 626 123 5555
'*******************************************

Dim strHold As String
Dim i As Integer
Dim n As Integer

strHold = Trim(pStr)
'check for entry
If UBound(varmyvals) < 0 Then Exit Function
For n = 0 To UBound(varmyvals())
Do While InStr(strHold, varmyvals(n)) > 0
i = InStr(strHold, varmyvals(n))
strHold = Left(strHold, i - 1) & Mid(strHold, i +
Len(varmyvals(n)))
Loop
Next n
ChopIt = Trim(strHold)

End Function

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.