Problem with spaces 1

[dpav29]

I need some advice on how to get rid of a bunch of spaces in name field.

There are 250,000 records and the name field is a mess. The format is FIRST MI LAST with no comma's. The problem is that there is almost always multiple spaces between the name parts. It looks like this:

JOHN M SMITH
SALLY JONES
FRED M JOHNSON

There are no preceding or trailing spaces. I thought of using find/replace, but I can't delete all spaces. . still need a space between the name parts.

In the end, I'm going to parse out fist MI last into seperate fields, so maybe all these spaces don't matter. Trouble is, I don't know how to use Trim with all those spaces there.

Thanks as always for any help!

 

[Golom]

You could

UPDATE tbl SET NameField = Replace(NameField,"  "," ")

which will replace double spaces with single ones. Run it a few times to get rid of multiple embedded spaces.

 

[HitechUser]

Try this..

http://www.tek-tips.com/viewthread.cfm?qid=886479

or this..

http://www.tek-tips.com/viewthread.cfm?qid=639652

'This code from the above link...
Public Function SingleSpace(InText As String) As String
   Dim S As String
    S = InText
  Do
    S = Replace(S, "  ", " ")
   Loop Until InStr(1, S, "  ") = 0
   SingleSpace = S
 End Function

or maybe this...

http://www.tek-tips.com/viewthread.cfm?qid=638913

Good Luck...

 

[dpav29]

Thanks! Golom's suggestion was the easiest!

Sorry for not finding the threads myself. . I became frustrated while searching. For some reason, after reading a thread I can no longer click the back but to get back to the search results. I have to keep typing in the same search over and over. I know it sounds stupid, but I'm about to jump out the window. (first floor)

Thanks again!

 

[Golom]

You're welcome ... and it could be worse.

You could be in the basement!

 

[PHV]

For some reason, after reading a thread I can no longer click the back but to get back to the search results
Simply open each search result in a new browser's window.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HitechUser]

Golom,

Got to hand it to ya! Quite simple. I never thought about running it multile times. Coooooooool..

Thanks..

 

[dpav29]

And I started doing it that way using find/replace. Access will only replace around 8000 at a time, so . . .