Update Query to replace double spaces with single space

[ChelleV]

I'm not trying to post this twice, but I'm concerned no
one will see my other post, as it is nested inside another
message.

I've been working with an Update Query to change double
spaces in a text field to single spaces, unless the double
space follows a period (.). I can get the records I want
by adding this criteria to the query:
Like "* *" And Not Like "*. *"

However, how can I write the UPDATE TO line? When I put
in "* *" it's obviously replacing the line with * *. I
only want to replace the double space within the text with
the single space, not the entire record with a single
space.

(Getting double spaces in this field is an ongoing
problem, so I want a fix that the user can run on records
in batches, or a macro that runs 'on change'.)

Thanks again,
ChelleV

 

[ChelleV]

Please disregard - John answered my question in another
email. Thanks!

 

[epadilla]

I know it's an old post but it helped me. This is what I was able to create with the information I found here.

Using SQL view on Access 2010:

UPDATE [Table_Name] SET [Table_Name].TestCaseHeadline = Replace([Field_Name]," "," ")
WHERE ((([Table_Name].Field Name) Like "* *"));