Solved

How to remove spaces

Posted on 2006-10-28
14
281 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now