Remove Excess Spaces

[EllenM]

Hello,
I have a data that contains excess spaces. I'd like to remove mulitple
spaces and replace them with a single space when they appear in my report.
Can someone help me with this?

Thanks in advance,
Ellen

 

[Duane Hookom]

I would create a small function like:
Function RemoveMultiples(pstrText As String, _
pstrChar As String) As String
Dim strReturn As String
Dim str2Chars As String
str2Chars = String(2, pstrChar)
Do Until InStr(pstrText, str2Chars) = 0
pstrText = Replace(pstrText, str2Chars, pstrChar)
Loop
RemoveMultiples = pstrText
End Function
You can then use this in a control source like:
=RemoveMultiples([Your Expression or Field]," ")

 

[EllenM]

Thanks, Duane. Could you give me details on how to use a function? Do I add
it somewhere in the properties box? I'd like to remove the multiple spaces
in the table itself. Would that be easier to do.

Thanks in advance,
Ellen

I would create a small function like:
Function RemoveMultiples(pstrText As String, _
pstrChar As String) As String
Dim strReturn As String
Dim str2Chars As String
str2Chars = String(2, pstrChar)
Do Until InStr(pstrText, str2Chars) = 0
pstrText = Replace(pstrText, str2Chars, pstrChar)
Loop
RemoveMultiples = pstrText
End Function
You can then use this in a control source like:
=RemoveMultiples([Your Expression or Field]," ")
--
Duane Hookom
Microsoft Access MVP

Hello,
I have a data that contains excess spaces. I'd like to remove mulitple
spaces and replace them with a single space when they appear in my report.
Can someone help me with this?

Thanks in advance,
Ellen

 

[Duane Hookom]

If you want to do it in the table then you should be able to open the table
in datasheet view and edit replace space space with space. Otherwise you
would open a new blank module and paste the code into the module window. Save
the module as "modStringFunctions".

You would then be able to use the function lots of places including an
update query.
UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," ")
WHERE [NoFieldNameGiven] Is Not Null;

--
Duane Hookom
Microsoft Access MVP

Thanks, Duane. Could you give me details on how to use a function? Do I add
it somewhere in the properties box? I'd like to remove the multiple spaces
in the table itself. Would that be easier to do.

Thanks in advance,
Ellen

I would create a small function like:
Function RemoveMultiples(pstrText As String, _
pstrChar As String) As String
Dim strReturn As String
Dim str2Chars As String
str2Chars = String(2, pstrChar)
Do Until InStr(pstrText, str2Chars) = 0
pstrText = Replace(pstrText, str2Chars, pstrChar)
Loop
RemoveMultiples = pstrText
End Function
You can then use this in a control source like:
=RemoveMultiples([Your Expression or Field]," ")
--
Duane Hookom
Microsoft Access MVP

Hello,
I have a data that contains excess spaces. I'd like to remove mulitple
spaces and replace them with a single space when they appear in my report.
Can someone help me with this?

Thanks in advance,
Ellen

 

[EllenM]

Thanks so much, Duane. It works quite well!! Is there a way to clear out
the multiple spaces of several fields with one query?

If you want to do it in the table then you should be able to open the table
in datasheet view and edit replace space space with space. Otherwise you
would open a new blank module and paste the code into the module window. Save
the module as "modStringFunctions".

You would then be able to use the function lots of places including an
update query.
UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," ")
WHERE [NoFieldNameGiven] Is Not Null;

--
Duane Hookom
Microsoft Access MVP

Thanks, Duane. Could you give me details on how to use a function? Do I add
it somewhere in the properties box? I'd like to remove the multiple spaces
in the table itself. Would that be easier to do.

Thanks in advance,
Ellen

I would create a small function like:
Function RemoveMultiples(pstrText As String, _
pstrChar As String) As String
Dim strReturn As String
Dim str2Chars As String
str2Chars = String(2, pstrChar)
Do Until InStr(pstrText, str2Chars) = 0
pstrText = Replace(pstrText, str2Chars, pstrChar)
Loop
RemoveMultiples = pstrText
End Function
You can then use this in a control source like:
=RemoveMultiples([Your Expression or Field]," ")
--
Duane Hookom
Microsoft Access MVP


:

Hello,
I have a data that contains excess spaces. I'd like to remove mulitple
spaces and replace them with a single space when they appear in my report.
Can someone help me with this?

Thanks in advance,
Ellen

 

[Duane Hookom]

Not sure if you have any fields with null values so try:
UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," "),
[NoFieldNameGiven2] = RemoveMultiples([NoFieldNameGiven2]," ")
WHERE [NoFieldNameGiven] Is Not Null and [NoFieldNameGiven2] Is Not Null;

--
Duane Hookom
Microsoft Access MVP

Thanks so much, Duane. It works quite well!! Is there a way to clear out
the multiple spaces of several fields with one query?

If you want to do it in the table then you should be able to open the table
in datasheet view and edit replace space space with space. Otherwise you
would open a new blank module and paste the code into the module window. Save
the module as "modStringFunctions".

You would then be able to use the function lots of places including an
update query.
UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," ")
WHERE [NoFieldNameGiven] Is Not Null;

--
Duane Hookom
Microsoft Access MVP

Thanks, Duane. Could you give me details on how to use a function? Do I add
it somewhere in the properties box? I'd like to remove the multiple spaces
in the table itself. Would that be easier to do.

Thanks in advance,
Ellen

:

I would create a small function like:
Function RemoveMultiples(pstrText As String, _
pstrChar As String) As String
Dim strReturn As String
Dim str2Chars As String
str2Chars = String(2, pstrChar)
Do Until InStr(pstrText, str2Chars) = 0
pstrText = Replace(pstrText, str2Chars, pstrChar)
Loop
RemoveMultiples = pstrText
End Function
You can then use this in a control source like:
=RemoveMultiples([Your Expression or Field]," ")
--
Duane Hookom
Microsoft Access MVP


:

Hello,
I have a data that contains excess spaces. I'd like to remove mulitple
spaces and replace them with a single space when they appear in my report.
Can someone help me with this?

Thanks in advance,
Ellen

 

[EllenM]

Thanks soooooo much!! It works beautifully!!!

Not sure if you have any fields with null values so try:
UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," "),
[NoFieldNameGiven2] = RemoveMultiples([NoFieldNameGiven2]," ")
WHERE [NoFieldNameGiven] Is Not Null and [NoFieldNameGiven2] Is Not Null;

--
Duane Hookom
Microsoft Access MVP

Thanks so much, Duane. It works quite well!! Is there a way to clear out
the multiple spaces of several fields with one query?

If you want to do it in the table then you should be able to open the table
in datasheet view and edit replace space space with space. Otherwise you
would open a new blank module and paste the code into the module window. Save
the module as "modStringFunctions".

You would then be able to use the function lots of places including an
update query.
UPDATE [tblNoNameGiven]
SET [NoFieldNameGiven] = RemoveMultiples([NoFieldNameGiven]," ")
WHERE [NoFieldNameGiven] Is Not Null;

--
Duane Hookom
Microsoft Access MVP


:

Thanks, Duane. Could you give me details on how to use a function? Do I add
it somewhere in the properties box? I'd like to remove the multiple spaces
in the table itself. Would that be easier to do.

Thanks in advance,
Ellen

:

I would create a small function like:
Function RemoveMultiples(pstrText As String, _
pstrChar As String) As String
Dim strReturn As String
Dim str2Chars As String
str2Chars = String(2, pstrChar)
Do Until InStr(pstrText, str2Chars) = 0
pstrText = Replace(pstrText, str2Chars, pstrChar)
Loop
RemoveMultiples = pstrText
End Function
You can then use this in a control source like:
=RemoveMultiples([Your Expression or Field]," ")
--
Duane Hookom
Microsoft Access MVP


:

Hello,
I have a data that contains excess spaces. I'd like to remove mulitple
spaces and replace them with a single space when they appear in my report.
Can someone help me with this?

Thanks in advance,
Ellen