Is there a way to remove extra spaces xxxxxxxxxxxxxx

If I want this in a text field:

Applesauce, 19x25, Green, Vertical, Chicago

But have this instead:

Applesauce,  19x25,  Green, Vertical, Chicago

(extra space before the "19" and also before the "Green") is there a way to remove them?

There is to be just one space after each comma.

--Steve
SteveL13Asked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
@SteveL13

Please run the following query:
Select ProdRef, Replace(ProdRef, "  ", " ") 
From tblProducts 
Where Instr(ProdRef, "  ") <> 0

Open in new window

0
 
COACHMAN99Commented:
use replace function, and substitute ' ' for '  '.
e.g. Text41 = Replace(Text41, "  ", " ")
0
 
aikimarkCommented:
You might need to put that in a loop
Do
    dbEngine(0)(0).Execute "Update MyTable Set MyField = Replace(MyField, ""  "", "" "") Where Instr(MyField, ""  "") <> 0"
Loop Until dbEngine(0)(0).Recordsaffected = 0

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
SteveL13Author Commented:
I'm not sure this is working.  Here is my code with my table and field names in place.

Private Sub Command0_Click()

    Do
    DBEngine(0)(0).Execute "Update tblProducts Set ProdRef = Replace(ProdRef, ""  "", "" "") Where Instr(ProdRef, ""  "") <> 0"
    Loop Until DBEngine(0)(0).RecordsAffected = 0

End Sub

I created a form with a command button to run the code but it is not removing the extra spaces.
0
 
aikimarkCommented:
Have you refreshed your view of the data?  You might need to requery the tblProducts to see the changes.
0
 
SteveL13Author Commented:
Yes,  I had the product table open and saw the record with the extra spaces.  Then I closed it and ran the command button on the form.  The re-opened the product table and the record still has the extra spaces.

Just to replay the topic:

If I want this in a text field:

Applesauce, 19x25, Green, Vertical, Chicago

But have this instead:

Applesauce,  19x25,  Green, Vertical, Chicago

(extra space before the "19" and also before the "Green") is there a way to remove them?

There is to be just one space after each comma.
0
 
aikimarkCommented:
The Replace() function should do that.  Is it possible that those really aren't spaces you're seeing?
0
 
COACHMAN99Commented:
you don't need a loop if there are always 1 or 2 spaces
Text41 = Replace(Text41, "  ", " ")
run it twice if necessary (three or 4 spaces)
e.g.
Text41 = Replace(Replace(Text41, "  ", " ") , "  ", " ")
0
 
aikimarkCommented:
@coachman99

The repeated execution is to catch any odd number of spaces (3,5,7, etc) that won't get eliminated by a single execution of the Replace() function.  The fact that the Replace() function is not eliminating any duplicate spaces is troubling.
0
 
SteveL13Author Commented:
Regarding:

Is it possible that those really aren't spaces you're seeing?

Yes.  I can manually remove them.
0
 
SteveL13Author Commented:
Getting compile error: Expected: Case
0
 
SteveL13Author Commented:
Sorry, I tried to run that with my command button.  When I run that as a query I get one result which is what I expected with the before and after fields displayed.  The commas were removed in the "after" field.
0
 
aikimarkCommented:
Getting compile error: Expected: Case
This question is in the MS Access zone.  Are you running this in a SQL Server environment?

The commas were removed
Did you mean the extra space characters were removed?

Is your tblProducts table read only?
0
 
SteveL13Author Commented:
No. It belongs in MS Access zone. I just pasted your code in a query SQL window.  Yes, the second filed shows the extra spaces removed.
0
 
aikimarkCommented:
But the Update query didn't change any of the rows?
0
 
aikimarkCommented:
I ran a test on a local Access database and the extra spaces were removed by the Update SQL statement.  I even put it inside the Do...Loop to test that recommendation as well..  I don't know what could be happening on your system in your database.
0
 
aikimarkCommented:
@SteveL13

What did you discover or do to get the query to work in your run-time environment?
0
 
SteveL13Author Commented:
Actually your code...

Select ProdRef, Replace(ProdRef, "  ", " ")
From tblProducts
Where Instr(ProdRef, "  ") <> 0

Showed only one record that had the extra spaces in it.  So I just used the results to copy/paste the corrected data into the field in the table.

Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.