Solved

Is there a way to remove extra spaces xxxxxxxxxxxxxx

Posted on 2014-03-17
18
201 Views
Last Modified: 2014-03-24
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
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 2
18 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39934883
use replace function, and substitute ' ' for '  '.
e.g. Text41 = Replace(Text41, "  ", " ")
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39935890
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
 

Author Comment

by:SteveL13
ID: 39936857
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 45

Expert Comment

by:aikimark
ID: 39937066
Have you refreshed your view of the data?  You might need to requery the tblProducts to see the changes.
0
 

Author Comment

by:SteveL13
ID: 39937628
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39937742
The Replace() function should do that.  Is it possible that those really aren't spaces you're seeing?
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39938415
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39938809
@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
 

Author Comment

by:SteveL13
ID: 39939829
Regarding:

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

Yes.  I can manually remove them.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 39939850
@SteveL13

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

Open in new window

0
 

Author Comment

by:SteveL13
ID: 39940025
Getting compile error: Expected: Case
0
 

Author Comment

by:SteveL13
ID: 39940035
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39940155
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
 

Author Comment

by:SteveL13
ID: 39940198
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39940424
But the Update query didn't change any of the rows?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39940454
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39950202
@SteveL13

What did you discover or do to get the query to work in your run-time environment?
0
 

Author Comment

by:SteveL13
ID: 39950493
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question