We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

How to remove spaces

kevinsrea
kevinsrea asked
on
Medium Priority
328 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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

Author

Commented:
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

It's a paragraph character.
Probably chr(13).
Try:

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

or (in code)

Replace(YourField,vbCrLf, "")
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
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

Author

Commented:
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


Author

Commented:
hi dannywareham..,
thanks for the notes.., but it does not work.. the space is still there.

kevin


Author

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Sorry to miss your posts.  I'm glad you got this sorted.
Kevinsrea - you cannot advertise here (it's against the member rules, unfortunately)

:-)

Author

Commented:
bummer.. sorry about that.

No worries
I've posted in CS to get it removed for you
:-)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.