Maybe I was not clear in my question. The spaces will be inconsistent in
the
text - some text may have 7 spaces and some may have more or less.
I found a solution from another thread. It worked well for me and it is
not
dependent on knowing how many excess spaces there are. This is from Duane
Hookom, a MS Access MVP. Hope it may help someone else... I attached
extracts
from the solution below.
***********************************
1. A function to check each character in the string and remove excess
spaces.
Function RemoveMultiples(pstrText As String, _
pstrChar As String) As String
Dim strReturn As String
Dim str2Chars As String
str2Chars = String(2, pstrChar)
Do Until InStr(pstrText, str2Chars) = 0
pstrText = Replace(pstrText, str2Chars, pstrChar)
Loop
RemoveMultiples = pstrText
End Function
You can then use this in a control source like:
=RemoveMultiples([Your Expression or Field]," ")
--
Duane Hookom
Microsoft Access MVP
2. How to use it in a function
If you want to do it in the table then you should be able to open the
table
in datasheet view and edit replace space space with space. Otherwise you
would open a new blank module and paste the code into the module window.
Save
the module as "modStringFunctions".
You would then be able to use the function lots of places including an
update query.
UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," ")
WHERE [NoFieldNameGiven] Is Not Null;
--
Duane Hookom
Microsoft Access MVP
3. To clear out the multiple spaces of several fields with one query...
UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," "),
[NoFieldNameGiven2] = RemoveMultiples([NoFieldNameGiven2]," ")
WHERE [NoFieldNameGiven] Is Not Null and [NoFieldNameGiven2] Is Not Null;
Duane Hookom
Microsoft Access MVP
*************************************
Gina Whipp said:
Looks like there are 7 spaces between fields so try this
Replace ([YourField]," "," ")
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
paulhk88 said:
Hi, this will remove all spaces. If there are more than one space
between
the
words, I want to remove the excess and keep only one so that it reads
right.
:
paulhk88,
Try...
Replace ([YourField]," ","")
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
I import data from spreadsheets into Access. Sometimes I get data
that
contain excess spaces within a field such as description. For
example:
"
AAA
BBB CCCCCCCCCC DDDDDD EEEE ". I have used TRIM
to
remove
the leading and trailing spaces but how do I remove unnecessary
spaces
within
the text. I want to keep the one space between words. Thanks
