Extra spaces in Access Records.

[Marco Gallo]

I've been trying to get rid of extra spaces in a table that I created
with addresses in it.

For instance in a field called TEST, I got the following address:

" 1 main st Apt A "
First I tried to use the "trim", "ltrim" and "rtrim" functions, these
functions did work, but only for those spaces at the beginning and at
the end of the record, so the result was:

"1 main st Apt A"

But I still haven't figure out how to get rid of the spaces in the
middle of the record, the ones in between the words. In excel the trim
function does the trick, but in access it does not.

Please let me know of a function or instruction that deletes these
spaces in between the words.

Any help is greatly appreciated.

Thanks.

[Salad]

Marco Gallo wrote:

I've been trying to get rid of extra spaces in a table that I created
with addresses in it.

For instance in a field called TEST, I got the following address:

" 1 main st Apt A "
First I tried to use the "trim", "ltrim" and "rtrim" functions, these
functions did work, but only for those spaces at the beginning and at
the end of the record, so the result was:

"1 main st Apt A"

But I still haven't figure out how to get rid of the spaces in the
middle of the record, the ones in between the words. In excel the trim
function does the trick, but in access it does not.

Please let me know of a function or instruction that deletes these
spaces in between the words.

Any help is greatly appreciated.

Thanks.

Call a function. You can drop the below function in a code module.
Then create an update query. Drag the field down to be updated. Lets
call it Address. In the Update To row of the query enter
RemExtraSpaces([Address])

You can test this function out by entering
? RemExtraSpaces("1 main st Apt A")
in the immediate window.

BTW, make a copy of the table first before you run...just to be sure.

One other note. It removes duplicate spaces only. If the first/last
chars are spaces, they remain.

Public Function RemExtraSpaces(strWord As Variant) As String
If Not IsNull(strWord) Then
Dim intFor As Integer

For intFor = 1 To Len(strWord)
RemExtraSpaces = RemExtraSpaces & _
Mid(strWord, intFor, 1)

If Mid(strWord, intFor, 1) = Space(1) Then
While Mid(strWord, intFor, 1) = Space(1) And _
intFor < Len(strWord)

intFor = intFor + 1
Wend
intFor = intFor - 1
End If
Next intFor
End If
End Function

[WindAndWaves]

I think you should write a little procedure along these lines:
Function GRS(StrS As String) As String
'gets rid of spaces in strings
On Error GoTo er
'--------------------
Dim StrS2 As String
Dim StrS3 As String
Dim i As Long
'--------------------
StrS = Trim(StrS)
For i = 1 To Len(StrS)
StrS2 = Mid(StrS, i, 1)
If StrS2 = " " And StrS3 = " " Then
'do nothing
Else
GRS = GRS & StrS2
End If
StrS3 = StrS2
Next i
xt:
Exit Function
er:
GRS = StrS
Resume xt
End Function

"Marco Gallo" <mw*****@tecopartners.com> wrote in message
news:93*************************@posting.google.co m...

I've been trying to get rid of extra spaces in a table that I created
with addresses in it.

For instance in a field called TEST, I got the following address:

" 1 main st Apt A "
First I tried to use the "trim", "ltrim" and "rtrim" functions, these
functions did work, but only for those spaces at the beginning and at
the end of the record, so the result was:

"1 main st Apt A"

But I still haven't figure out how to get rid of the spaces in the
middle of the record, the ones in between the words. In excel the trim
function does the trick, but in access it does not.

Please let me know of a function or instruction that deletes these
spaces in between the words.

Any help is greatly appreciated.

Thanks.

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

[Imboden]

Maybe something like this:

Function onespace(pStr As String) As String
'*******************************************
'Purpose:   Removes excess spaces from a string
'Inputs:    ? onespace(" the    quick    brown fox   ")
'Output:    "the quick brown fox"
'*******************************************
 
Dim strHold As String
 
strHold = RTrim(pStr)
 
Do While InStr(strHold, "  ") > 0 '"  " = 2 spaces
strHold = Left(strHold, InStr(strHold, "  ") - 1) & Mid(strHold,
InStr(strHold, "  ") + 1)
Loop
 
onespace = Trim(strHold)
 
End Function
 

Best wishes, Imboden

[rkc]

"Imboden" <im*****@zalau.ro> wrote in message
news:bb**************************@posting.google.c om...

Maybe something like this:

 Function onespace(pStr As String) As String
 '*******************************************
 'Purpose:   Removes excess spaces from a string
 'Inputs:    ? onespace(" the    quick    brown fox   ")
 'Output:    "the quick brown fox"
 '*******************************************

 Dim strHold As String

 strHold = RTrim(pStr)

     Do While InStr(strHold, "  ") > 0 '"  " = 2 spaces
       strHold = Left(strHold, InStr(strHold, "  ") - 1) & Mid(strHold,
 InStr(strHold, "  ") + 1)
     Loop

     onespace = Trim(strHold)

 End Function
 

s = trim (s)
do while instr(s," ") > 0
s = replace(s, " ", " ")
loop

[Imboden]

> s = trim (s)

do while instr(s," ") > 0
s = replace(s, " ", " ")
loop

Right!

Should have included:

Written in A97 (no Replace() function exists)

Best Wishes, Imboden