Access Poser

[steven H]

I realise this is not the place.... But my recordset is nearly 1 million. So Access would be the easiest way forward.

I have tel numbers containing all sorts of spaces, dashes etc,in one column. I want to run an update query to an empty column which removes blanks and dashes leaving the tel no as a the numbers

ie 01253-202121 would be 01253202121

This is in order that I can lookup and match on telno from another list. Any help would be appreciated

[Al Chara]

I'm not familiar with the VBA for Access, but in excel you could do it in the following way:

Sub OnlyNumbers()
Dim FinalStr As String
For i = 1 To Len(ActiveCell.Value)
If IsNumeric(Mid(ActiveCell.Value, i, 1)) = True Then FinalStr = FinalStr + Mid(ActiveCell, i, 1)
Next
ActiveCell.Value = FinalStr

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-04-18 10:10 ]

[steven H]

thanks Al, thats great, its just the volume of numbers....

[Al Chara]

If you could get your data into excel then you could throw the code into a FOR loop that will do EACH cell in the ACTIVESHEET's USEDRANGE.