Getting rid of spaces in fields

[Stavrogin]

Hi,

I have a database of about 10,000 individuals. One column is devoted
to a person's surname (LastName) and another column to their first name
and inititals (FirstName).

One problem I have is that the spacing is all screwed up in the
FirstName column. For example, instead of reading John_HS (where _ is
a space), it will read John__H_S__ or John__H__S_.

Any ideas on how to get rid of this superfluous spacing?
Thanks,
Stavrogin.

[Fatima]

Stavrogin,

I don't think this is the perfect solution
but you can remove all spaces from your text except for single spaces
betwee words using Excel
You have to export your table to an Excel file then use the "Trim"
function on the FirstName column
then import it agin in your Access file

[Fatima]

Stavrogin,

I don't think this is the perfect solution
but you can remove all spaces from your text except for single spaces
betwee words using Excel
You have to export your table to an Excel file then use the "Trim"
function on the FirstName column
then import it agin in your Access file

Fatiam

[Bob Quintal]

"Stavrogin" <si******@dccnet.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

Hi,

I have a database of about 10,000 individuals. One column is
devoted to a person's surname (LastName) and another column to
their first name and inititals (FirstName).

One problem I have is that the spacing is all screwed up in
the FirstName column. For example, instead of reading John_HS
(where _ is a space), it will read John__H_S__ or John__H__S_.

Any ideas on how to get rid of this superfluous spacing?
Thanks,
Stavrogin.

If you have Access 2000 or newer, use the replace function in an
update query to replace two spaces with one.

If you are using Access '97, you can find several examples of
how to write your own replace function.
--
Bob Quintal

PA is y I've altered my email address.

[Phil Stanton]

Try

Option Compare Database
Option Explicit

Function EliminateSpaces(FirstName As String) As String

Dim i As Integer, j As Integer
Dim Letter As String, OutputStg As String

i = 1
Check:
While i < Len(FirstName) - 1
Letter = Mid$(FirstName, i, 1)
If Letter = " " And Mid$(FirstName, i + 1, 1) = " " Then
i = i + 1
GoTo Check
Else
OutputStg = OutputStg & Mid$(FirstName, i, 1)
End If
i = i + 1
Wend

EliminateSpaces = Trim(OutputStg)

End Function
Phil
"Stavrogin" <si******@dccnet.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi,

I have a database of about 10,000 individuals. One column is devoted
to a person's surname (LastName) and another column to their first name
and inititals (FirstName).

One problem I have is that the spacing is all screwed up in the
FirstName column. For example, instead of reading John_HS (where _ is
a space), it will read John__H_S__ or John__H__S_.

Any ideas on how to get rid of this superfluous spacing?
Thanks,
Stavrogin.

[Trevor Best]

Bob Quintal wrote:

If you have Access 2000 or newer, use the replace function in an
update query to replace two spaces with one.
If there's more than 2 spaces, you will need to do several passes on the
query, I'd recommend

update table set firstname = Replace(Firstname," "," ") where FirstName
Like "* *"

Then repeat execution until you get no records updated. (Confirm Action
Queries On and Setwarnings On and put up with the messages for a bit)
If you are using Access '97, you can find several examples of
how to write your own replace function.

Function ReplaceString(pstrtext As String, pstrFind As String,
pstrReplace As String)
Dim i As Long
Dim strText As String
strText = pstrtext

i = 1
i = InStr(i, strText, pstrFind)
Do While i
strText = Left$(strText, i - 1) & pstrReplace & Mid$(strText, i
+ Len(pstrFind))
i = i + Len(pstrReplace)
i = InStr(i, strText, pstrFind)
Loop
ReplaceString = strText
End Function

[Bob Quintal]

Trevor Best <no****@localhost.invalid> wrote in
news:43***********************@news.zen.co.uk:

Function ReplaceString(pstrtext As String, pstrFind As String,
pstrReplace As String)
Dim i As Long
Dim strText As String
strText = pstrtext

i = 1
i = InStr(i, strText, pstrFind)
Do While i
strText = Left$(strText, i - 1) & pstrReplace &
Mid$(strText, i
+ Len(pstrFind))
i = i + Len(pstrReplace)
i = InStr(i, strText, pstrFind)
Loop
ReplaceString = strText
End Function

If you change your code slightly to i = InStr(1, strText,
pstrFind)
in both places, the code will take out all occurences of
multiple spaces in one pass. Much faster than rerunning the
query. Beware of replacing x with xy, as that will loop forever.

Function ReplaceString(pstrtext As String, pstrFind As String,
pstrReplace As String)
Dim i As Long
Dim strText As String
strText = pstrtext

i = 1
i = InStr(1, strText, pstrFind)
Do While i
strText = Left$(strText, i - 1) & pstrReplace & Mid
$(strText, i + Len(pstrFind))
i = i + Len(pstrReplace)
i = InStr(1, strText, pstrFind)
Loop
ReplaceString = strText
End Function
--
Bob Quintal

PA is y I've altered my email address.

[Bob]

Hi,

For A97 users (it also works in later version) this will condense
multiple spaces to a single space. It won't, however, resolve the
problem of John_H_S, where all of the spaces are already single spaces.
That scenario is going to take some coding with validation rules as to
what's appropriate and what's not.

Function OneSpace(pstr As String) As String

'*******************************************
'Purpose: Removes excess spaces from a string
'Input: ? onespace(" now is the time for all good men ")
'Output: "now is the time for all good men"
'*******************************************

Dim strHold As String
strHold = RTrim(pstr)
Do While InStr(strHold, " ") > 0
strHold = Left(strHold, InStr(strHold, " ") - 1) & Mid(strHold,
InStr(strHold, " ") + 1)
Loop
OneSpace = Trim(strHold)

End Function

HTH - Bob

[Trevor Best]

Bob Quintal wrote:

If you change your code slightly to i = InStr(1, strText,
pstrFind)
in both places, the code will take out all occurences of
multiple spaces in one pass. Much faster than rerunning the
query. Beware of replacing x with xy, as that will loop forever.

Or just until the string gets to 2GB :-)

That's why I don't do that, I would assume people would want to use
Replace(something,"'","''") for insertion into an SQL string.

You could of course write a function to specifically remove duplicate
spaces, then apply that in the query, which would then only need one
pass. Personally I'd go that route to be specific about the function's
purpose and call it RemoveDupeSpace() or something rather than hack a
generic function to do something specific and limit it's use.

[Bob Quintal]

Trevor Best <no****@localhost.invalid> wrote in
news:43***********************@news.zen.co.uk:

Bob Quintal wrote:

If you change your code slightly to i = InStr(1, strText,
pstrFind)
in both places, the code will take out all occurences of
multiple spaces in one pass. Much faster than rerunning the
query. Beware of replacing x with xy, as that will loop
forever.

Or just until the string gets to 2GB :-)

That's why I don't do that, I would assume people would want
to use Replace(something,"'","''") for insertion into an SQL
string.

You could of course write a function to specifically remove
duplicate spaces, then apply that in the query, which would
then only need one pass. Personally I'd go that route to be
specific about the function's purpose and call it
RemoveDupeSpace() or something rather than hack a generic
function to do something specific and limit it's use.

We think alike.
--
Bob Quintal

PA is y I've altered my email address.