Extra spaces

[Ielamrani]

Hi,
I have a table with a field called Address.

Address

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

I would like to run a query that pulls only address that contains more than one space after the number.
in the above example I should only see the following in my new query:

12 New York Avenue
1 Penn Plaza
1235 Franklin Avenue


17 John Street should not be included because it has one space after number 17

Thanks

 

[PHV]

In the criteria cell of Address:
[tt]Like '*# *'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, hit submit too fast...
In the criteria cell of Address:
[tt]Like '*# *'[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ielamrani]

PHV you're amazing. I taught I know a lot of Vba but you prove to me that I have a long way to go..
I hope you do not mind me asking another similar question.

same address but the difference this time is: the spaces are not after the numbers, they're like this

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

on the first example the spaces are after John, 2nd example are after York,ect...
The only correct line is 123 Penn Plaza.

how do I run the query to give me all the worong ones:
17 John Street
12 New York Avenue
1 Penn Plaza
1235 Franklin Avenue
Thanks again

Ismail

 

[PHV]

[tt]Like '* ?*'[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ielamrani]

Thanks again