473,320 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to remove spaces in middle of the field?

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
6 48564
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
replace([field1]," ",:" ")

Mar 28 '06 #3
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
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
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
<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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Purdy | last post by:
I have an asp.net application. i export to excel, in exporting to excel i use an xslt to define the columns and look. on one of the fields i need the word 'qty' but i need it to look like ' ...
5
by: Marco Gallo | last post by:
I've been trying to get rid of extra spaces in a table that I created with addresses in it. For instance in a field called TEST, I got the following address: " 1 main st Apt A "...
3
by: Jeremy | last post by:
I'm trying to parse a sentence and want to remove selected words from the middle. Consider: dim a() as object = split(mySentence) for each w in a() select case w case w.startswith("X")
10
by: localpricemaps | last post by:
i have some html that looks like this <address style="color:#">34 main,<br> Boston, MA</address> and i am trying to use the replace function to get rid of the <Br> that i scrape out using...
3
by: Danny Yeadon | last post by:
Hi I need to remove unwanted characters from phone numbers from my phone bill to analyse the data. Some examples are 02 48222222 i need to remove the space +61266667656 ...
1
by: BethanyBez | last post by:
Is there any way when doing a merge with an Access Query and a columned Word doc that I can avoid blank spaces where the field is blank? I am creating a columned contact sheet with the contacts...
7
by: Bosconian | last post by:
I know that str.replace(/^\s+|\s+$/g,''); will trim a string of space, but what about removing extra spaces from the middle? Where "hello world"
4
by: kaushikshome | last post by:
Can anyone please help me in writing the code in C++ : I want a read a file line by line,remove the lines which have length of a particular field in a line exceeding 63 characters After removing...
3
by: YoungJohn | last post by:
I'm extracting data from a database to create a table. My table includes a text field 'Postcode' for postcodes. Sometimes the extracted postcodes are in the format SL37HY and in other...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.