Solved

How to remove spaces

Posted on 2006-10-28
14
285 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

740 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