By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,929 Members | 1,245 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,929 IT Pros & Developers. It's quick & easy.

Extra spaces in Access Records.

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
Maybe something like this:
Expand|Select|Wrap|Line Numbers
  1. Function onespace(pStr As String) As String
  2. '*******************************************
  3. 'Purpose:   Removes excess spaces from a string
  4. 'Inputs:    ? onespace(" the    quick    brown fox   ")
  5. 'Output:    "the quick brown fox"
  6. '*******************************************
  7.  
  8. Dim strHold As String
  9.  
  10. strHold = RTrim(pStr)
  11.  
  12. Do While InStr(strHold, "  ") > 0 '"  " = 2 spaces
  13. strHold = Left(strHold, InStr(strHold, "  ") - 1) & Mid(strHold,
  14. InStr(strHold, "  ") + 1)
  15. Loop
  16.  
  17. onespace = Trim(strHold)
  18.  
  19. End Function
  20.  
Best wishes, Imboden
Nov 13 '05 #4

P: n/a
rkc

"Imboden" <im*****@zalau.ro> wrote in message
news:bb**************************@posting.google.c om...
Maybe something like this:
Expand|Select|Wrap|Line Numbers
  1.  Function onespace(pStr As String) As String
  2.  '*******************************************
  3.  'Purpose:   Removes excess spaces from a string
  4.  'Inputs:    ? onespace(" the    quick    brown fox   ")
  5.  'Output:    "the quick brown fox"
  6.  '*******************************************
  7.  Dim strHold As String
  8.  strHold = RTrim(pStr)
  9.      Do While InStr(strHold, "  ") > 0 '"  " = 2 spaces
  10.        strHold = Left(strHold, InStr(strHold, "  ") - 1) & Mid(strHold,
  11.  InStr(strHold, "  ") + 1)
  12.      Loop
  13.      onespace = Trim(strHold)
  14.  End Function
  15.  

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

Nov 13 '05 #5

P: n/a
> 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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.