Spaces 1

[Ielamrani]

Hi,
How do I eliminate (Middle)spaces from an address?
I have a field called Address1 like this:

Address1

17 John Street
12 New York Avenue
1 Penn Plaza
1235 Franklin Avenue

the only correct line is 17 John Street because it has one space after 17. All the others I am trying to correct because they contain more than one space after the number. In other word, I would like to have only one space after the number.
I hope this is clear.
Thanks in advance

ismail

 

[Remou]

Did my suggestion in
Eliminating spaces
thread705-1163309
Not work? Or was it not suitable?

 

[SkipVought]

Hi,

Replace(expression, " ", " ")

replace 2 spaces with one space. You might have to run in a loop until no replacement takes place.

    s = "first    last"
    l = Len(s)
    Do
        p = l
        s = Replace(s, "  ", " ")
        l = Len(s)
    Loop Until p = l

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[jsteph]

If you have a lot of different string stuff like that, there is a Regular Expression VBA module, that works with versions above 97. RegExp will give you profound advantages in flexibility with find/replace operations like that.

--Jim

 

[Ed2020]

If you have Access 2000 or later you can use the two array functions Split() and Join() to do this:

Dim arrAddress() as String
dim strCorrectedAddress as String

arrAddress = Split("12 New York Avenue"," ")

strCorrectedAddress=Join(arrAddress," ")

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ielamrani]

Sorry guys I did not reply soon enough. I just got to my email. I created a form from my table and put the following code on "On Open"

While InStr(Address, " ") > 0
Address = Replace(Address, " ", " ")
Loop

the field code is Address.

Nothing changed. I tried all the other suggestions and same result.
I am not an expert in Vba, can I try it in a query? and how?

Thanks in advance.

Imsail

 

[Ielamrani]

Problem solved. Remou helped me. the following code by remou did it:

Function StripSpaces(strInput)
Do While InStr(strInput, " ") > 0
strInput = Replace(strInput, " ", " ")
Loop
StripSpaces = strInput
End Function

Your query in SQL view would look like:
SELECT StripSpaces([Address1]) AS NewAddress
FROM tblTable;

Thank you all

 

[SilentAiche]

IeIamrani,

When I started to post, I was a little irritated because I thought you had posted the the same question twice.

Instead, I found that you'd gotten a good solution to your problem and (this is the cool part), posted the solution and gave the author credit.

I apologize for my initial reaction. Please have a STAR and my personal thanks.

Tim

[blue]______________________________________________________________
I love logging onto Tek-Tips. It's always so exciting to see what the hell I
said yesterday. [/blue]

 

[Ielamrani]

do not worry. I did post it twice by mistake. The reason I did because I taught the first one did not go through.
Thanks for being honest and thanks for your help
Ismail

 

[Ielamrani]

I am having trouble sending this question as new, I do not know why. So I am sending here.

Hi,
I have 2 queries. I called them qrySend and qryFailed

qrySent has the following

Fax Jan Feb Mar
212-547-6547 5 6 2
516-547-6053 5 1 20
212-200-6547 78 20 15


qryFailed has the following

Fax Jan Feb Mar
212-547-6547 4 0 1
516-547-6053 1 1 0
212-200-6547 7 11 3

As you can see the fax is the same in both tables so I am using it as a unique id.

What I would like to do is make the 2 queries (qrySend and qryFailed) into one (qryfinal) and the result should be:

qryfinal

Fax Jan Feb Mar
212-547-6547 5 6 2
4 0 1

516-547-6053 5 1 20
1 1 0

212-200-6547 78 20 15
7 11 3

The idea is to put the numbers from qrySent on top of those from qryFailed in qryfinal

I hope this is not confusing

Thanks in advance

Ismail

 

[Remou]

I think a Union query should suit (SQL view):
[tt]SELECT qryFailed.Fax,"Failed" As SentFailed, qryFailed.Jan, qryFailed.Feb, qryFailed.Mar
FROM qryFailed
Union
SELECT qrySent.Fax,"Sent", qrySent.Jan, qrySent.Feb, qrySent.Mar
FROM qrySent[/tt]

 

[Ielamrani]

The query gave me exactly what I was looking for. You guys are good.
Thank you very much

Ismail