Solved

How to remove spaces

Posted on 2006-10-28
14
282 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

867 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

15 Experts available now in Live!

Get 1:1 Help Now