Solved

How to remove spaces

Posted on 2006-10-28
14
287 Views
Last Modified: 2012-08-14
hi guys,

i am trying to removes spaces in a name field.


some of the names are just like
John Jones., but with 2 spaces like  John  Jones
then some are like  
John J  Jones
that is with 2 spaces after the middle initial
and then some are like
John  J Jones., where there are 2 spaces in front of the middle initial.

Some of the initials have periods after them, some do not.

what i am trying to do is get the duplicates out of a mailing list..
but using just a regualr find duplicate query does not work.
any ideas ?
all of these are in one table in MS Access 2003

thanks,
kevin rea
0
Comment
Question by:kevinsrea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
14 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 125 total points
ID: 17827675
You can use Replace in an update query to find double spaces and replace them with singles ... MAKE A BACKUP FIRST!

UPDATE YourTable
SET YourField = Replace(YourField, "  ", " ")

Then try the find duplicates query again
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17827686
If you also want to remove the periods after the initials, you can nest replace functions, using a second replace function to replace periods with empty strings:

SET YourField = Replace(Replace(YourField, "  ", " "),".", "")

That will format your names to have single spaces between the parts of the names and no periods, so

John J. Jones
John   J Jones

Will both become John J Jones

0
 

Author Comment

by:kevinsrea
ID: 17827877
maybe it is not a  space.., i ran the update query as you said, and the spaces are there..,
so i am thinking that maybe it is a hidden character..

i copied one of the lines and pasted it into ms word, and then told word to show everything.,
and it showed a character in from of the name, where i thought was a space.

it showed a little circle in front of it.., so it must be some strange character, and not a space.
here is a picture of it that i copied to one of my websites:

http://www.avhomeinspector.com/images/space.jpg


so, i guess I have to try and figure out what this character is.., it does not seem to be a space.

thanks for your help.

kevin

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 26

Accepted Solution

by:
dannywareham earned 125 total points
ID: 17828422
It's a paragraph character.
Probably chr(13).
Try:

Replace(YourField,Chr(13), "")

or (in code)

Replace(YourField,vbCrLf, "")
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17828477
Kevin,

Jimpen suggested this code to get rid of any of those weird characters.  You can call it from a query as he describes in this thread:
http://www.experts-exchange.com/Databases/MS_Access/Q_21899816.html#16988050
0
 

Author Comment

by:kevinsrea
ID: 17829253
hi mbizup.

i can't figure out how to run that stripnonascii module in an update query..,
i tried in the updateto line:

stripnonascii([realtorname])
but it didn't like that..

any ideas ?

kevin


0
 

Author Comment

by:kevinsrea
ID: 17829255
hi dannywareham..,
thanks for the notes.., but it does not work.. the space is still there.

kevin


0
 

Author Comment

by:kevinsrea
ID: 17829321
hey gus,
i found this piece of code.. and it worked !!
**********************************************************
Public Function StripSpecialCharacters(ByVal sIn As String) _
As String

Dim sWkg As String, sOrigString As String, sNewString As String

Dim lLen As Long
Dim lCtr As Long, lCtr2 As Long
Dim sChar As String

lLen = Len(sIn)
'create buffer

sOrigString = Space$(lLen)
sOrigString = sIn
sNewString = sOrigString
lCtr2 = 1

For lCtr = 1 To lLen
    sChar = Mid(sOrigString, lCtr, 1)
    If Asc(sChar) < 128 Then
        Mid(sNewString, lCtr2, 1) = sChar
        lCtr2 = lCtr2 + 1
    End If
Next

If lCtr2 > 1 Then
    sNewString = Left(sNewString, lCtr2 - 1)
Else
    sNewString = ""
End If

StripSpecialCharacters = sNewString
End Function
******************************************************

kevin

0
 
LVL 61

Expert Comment

by:mbizup
ID: 17830149
Sorry to miss your posts.  I'm glad you got this sorted.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 17836337
Kevinsrea - you cannot advertise here (it's against the member rules, unfortunately)

:-)
0
 

Author Comment

by:kevinsrea
ID: 17836390
bummer.. sorry about that.

0
 
LVL 26

Expert Comment

by:dannywareham
ID: 17836402
No worries
I've posted in CS to get it removed for you
:-)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question