Replace third space with "-"

[Ljudmil]

Hi
I have a text field whose records contain for 1 to 4 words, separated by
space. I am wondering if I can replace automatically only third spaces with
“-“. Any ideas?
Ljudmil

 

[John Spencer]

Temporary or permanent replacement?

What version of Access?

An update query to make permanent replacement
UPDATE SomeTable
SET YourField = Left(YourField,InStrRev(YourField," ")-1) & "-" &
Mid(YourField,InStrRev(YourField," ")+1)
WHERE YourField Like "* * * *"

Temporary calculated field
IIF(YourField Like "* * * *",Left(YourField,InStrRev(YourField," ")-1) & "-" &
Mid(YourField,InStrRev(YourField," ")+1),YourField)

Both those could fail to give you the desired results if there are cases where
the words are separated by more than one space. For instance
A B would return A -B
A BC D would return A BC-D
ABC F would return ABC -F

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[vanderghast]

? Replace(Replace(Replace("aaa bbb ccc dddd eeee"," ", "&", Count:=2), " ",
"-", Count:=1), "&", " ")
aaa bbb ccc-dddd eeee

where I assumed that the character & is not present in the initial string.
Note the named argument syntax := (someone may miss the : )


Vanderghast, Access MVP

 

[Ljudmil]

I have tried the Update Query.
It works like a charm! Thanks!