spaces

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with many names that do not have uniform spacing inbetween.

ex john a smith
iam cheap

Is there a way that I can use a query to clean up the field so it's uniform

john a smith
iam cheap

Any help would be greatly appreaciated and time saving.

Thanks
 
I have a table with many names that do not have uniform spacing inbetween.

ex john a smith
iam cheap

Is there a way that I can use a query to clean up the field so it's uniform

john a smith
iam cheap

Any help would be greatly appreaciated and time saving.
Click to expand...

Depends on your version of Access. In AccessXP (with all service
packs) or later, just run an Update query updating the field (which
I'll call fullname) to

Replace([Fullname], " ", " ")

using a criterion on the namefield of

LIKE "* *"

Run this repeatedly until it doesn't find anything to change (only
twice if there are no more than four blanks in a row).


John W. Vinson[MVP]
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I usually write a VBA function to clean out the extra spaces & use an
UPDATE query to run it on the table. E.g.:

UPDATE table_name
SET column_name = TrimAll(column_name)
WHERE <criteria>

Function TrimAll(ByVal str As String) As String
' Purpose:
' Clean out all extraneous spaces in the string
' In:
' str The string to clean
' Out:
' String The resulting string
' Created:
' mgf 4feb2005
' Modified:
'

Const SPACE = " " ' 1 space char

Dim InWord As Boolean
Dim strTemp As String
Dim c As String
Dim i As Integer

str = Trim$(str)
i = 1
InWord = False

Do While i <= Len(str)
c = Mid$(str, i, 1)
' Check for all "white spaces"
If c = SPACE Or c = vbCr Or c = vbLf Or c = Chr$(9) Then
If InWord Then
strTemp = strTemp & SPACE
InWord = False
End If
Else
InWord = True
strTemp = strTemp & c
End If
i = i + 1
Loop

TrimAll = Trim$(strTemp)

End Function

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgQfEIechKqOuFEgEQIo6gCdHcH8352hrH5195kviy5o4BjgH9cAoNLh
BvD5P8fg512fvdO1CxbmPPe0
=G1p0
-----END PGP SIGNATURE-----
 
Thanks much

John Vinson said:
I have a table with many names that do not have uniform spacing inbetween.

ex john a smith
iam cheap

Is there a way that I can use a query to clean up the field so it's uniform

john a smith
iam cheap

Any help would be greatly appreaciated and time saving.
Click to expand...

Depends on your version of Access. In AccessXP (with all service
packs) or later, just run an Update query updating the field (which
I'll call fullname) to

Replace([Fullname], " ", " ")

using a criterion on the namefield of

LIKE "* *"

Run this repeatedly until it doesn't find anything to change (only
twice if there are no more than four blanks in a row).


John W. Vinson[MVP]
Click to expand...
 
Back
Top