asked on

How to remove spaces

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

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

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

kevinsrea

ASKER

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

ASKER CERTIFIED SOLUTION

dannywareham

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

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:
https://www.experts-exchange.com/questions/21899816/transfered-email-messages-from-outlook-to-access-then-to-excel.html#16988050

kevinsrea

ASKER

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

kevinsrea

ASKER

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

kevin

kevinsrea

ASKER

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

mbizup

Sorry to miss your posts. I'm glad you got this sorted.

dannywareham

Kevinsrea - you cannot advertise here (it's against the member rules, unfortunately)

:-)

kevinsrea

ASKER

bummer.. sorry about that.

dannywareham

No worries
I've posted in CS to get it removed for you
:-)