Solved

Is there a way to remove extra spaces xxxxxxxxxxxxxx

Posted on 2014-03-17
18
199 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

808 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