By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,610 Members | 1,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,610 IT Pros & Developers. It's quick & easy.

How to remove spaces in middle of the field?

P: n/a
I used replace function, but it doesn't work too good because don't
know how many spaces on the field. Do Access have wildcard "+" (one or
more)?

Mar 28 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On 28 Mar 2006 11:31:59 -0800, ca*******@hotmail.com wrote:
I used replace function, but it doesn't work too good because don't
know how many spaces on the field. Do Access have wildcard "+" (one or
more)?


Replace should work just fine.
Perhaps you didn't use it correctly.
Why not post an example of what you have, and what you would like to
get back?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Mar 28 '06 #2

P: n/a
replace([field1]," ",:" ")

Mar 28 '06 #3

P: n/a
On 28 Mar 2006 15:43:42 -0800, ca*******@hotmail.com wrote:
replace([field1]," ",:" ")


When replying to a message, it's good practice to include the relevant
part of the previous message.
No one here charges by the word.

What does this message tell me?
What's the semicolon doing there?
Are you trying to replace 2 spaces with 1?

[Field1] = Replace([field1]," "," ")

Is there a reason why you didn't give the rest of the information I
asked for?
What does your existing data look like?
What do you want it to look like after any changes?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Mar 29 '06 #4

P: n/a
For example:
[Field 1] = "text on google group "

replace([field1]," "," ")
->[Field 1] = "text on google group "

I want to get:
->[Field 1] = "text on goole group"

I know modules can do it, but I like the easy way, please help!!!

Mar 31 '06 #5

P: n/a
On 31 Mar 2006 14:46:22 -0800, ca*******@hotmail.com wrote:
For example:
[Field 1] = "text on google group "

replace([field1]," "," ")
->[Field 1] = "text on google group "

I want to get:
->[Field 1] = "text on goole group"

I know modules can do it, but I like the easy way, please help!!!


SELECT Trim(Replace([Field1]," "," ") )AS Exp, Replace([Exp]," ","
") AS exp2, Replace([Exp2]," "," ") AS NewSentence
FROM YourTable;

Note that the first Replace replaces 3 spaces with one. The second and
third replace 2 spaces with one. NewSentence is the completed
sentence.
It appears to work well for me.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Apr 1 '06 #6

P: n/a
<ca*******@hotmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
For example:
[Field 1] = "text on google group "

replace([field1]," "," ")
->[Field 1] = "text on google group "

I want to get:
->[Field 1] = "text on goole group"

I know modules can do it, but I like the easy way, please help!!!


use the replace function (replacing 2 spaces with 1 space) in a loop. Have
the loop continue until the length of the field remains the same (ie: no
double spaces found).

air code:

Do While True
Mlen1 = Len([Me.Field])
[Me.Field] = Replace([Me.Field]," "," ") '2 spaces replaced with 1
space
Mlen2 = Len([Me.Field])
If Mlen2 = Mlen1 Then
Exit Do
Endif
Loop

Fred Zuckerman
Apr 3 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.