Deleting spaces

[Guest]

I have a field in a table which holds the names of students (called
candname). Because of the way in which the data is captured, I often finish
up with 2 spaces between one name and the next - eg between the first name
and the surname. Could you help me with a query or some code which will scan
each name and whenever 2 spaces are found replace with 1 space.

It must be easy, but I can't think how to do it.

Many thanks - as ever!

 

[Marshall Barton]

I have a field in a table which holds the names of students (called
candname). Because of the way in which the data is captured, I often finish
up with 2 spaces between one name and the next - eg between the first name
and the surname. Could you help me with a query or some code which will scan
each name and whenever 2 spaces are found replace with 1 space.

UPDATE table
SET candname = Replace(candname, " "," ")
WHERE canname Like "* *"

Note that if there were three spaces, you would end up with
two.

 

[Graham R Seach]

Jim,

What you do depends on which version of Access you're using. See the
following page for examples:
http://www.pacificdb.com.au/MVP/Code/repl.htm

Then you can use the calling convention Marshall suggests:
UPDATE table
SET candname = Replace(candname, " "," ")
WHERE canname Like "* *"
....or...
UPDATE table
SET candname = Repl(candname, " "," ")
WHERE canname Like "* *"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia