Incomplete address

[Ielamrani]

Hi,
I have a table with a field called Address. I am trying to run a query to give only incomplete addresses, can someone tell me how to go about this?

Example:

Address
1787 New York Ave
35 Smith St
3 John Street
17 Avenue

My query should show only:

1787 New York Ave because Ave should be spelled Avenue
35 Smith St because st should be spelled Street

thanks

Ismail

 

[PHV]

SELECT *
FROM Address1
WHERE STREET <> FullDesc([STREET])

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

 

[Ielamrani]

Did not work for me.
Let's say I have a new table called AddressInfo with a field called Address.
The field address contains the words Ave, St, etc
Do I run a new query? If yes what's the criteria?

Thanks in advance

 

[PHV]

SELECT *
FROM AddressInfo
WHERE Address <> FullDesc([Address])

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 for you quick response. I am sorry, Any idea why I am getting the following error message:

syntax error(missing operator) in a query operation
'abbrev='BRYANT'S';'.

 

[PHV]

Replace this:[tt]
Where Abbrev='" & a(i) & "'", [/tt]
with this:[tt]
Where Abbrev='" & Replace(a(i), "'", "''") & "'", [/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]

The errro message is gone and the query ran but...
This is what I got:

Address

31-47 77TH ST
80 OLD RIVERHEAD RD
80 RIVERHEAD RD
86 E 49TH STREET
17 N MONTGOMERY AVENUE
340 E 4TH STREET
5 E 8H STREET
36A E 36 STREET
34 W 17 STREET
59 W 12 STREET
66 20 108 ST
38 W 32 STREET

I am only interested to see the ones ending with St, Ave, RD, etc

Thanks

 

[PHV]

SELECT *
FROM AddressInfo
WHERE Mid(Address,1+InStrRev(Address,' ')) In (SELECT Abbrev FROM AddrAbbrev)

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, As usual you're amazing...I am going to be at work Tuesday so I'll try the new code and I'll let you know.

Ismail

 

[Ielamrani]

Man, you're good. It worked.
I really appreciate all you guys help and being generous in sharing your knowledge.

Ismail

 

[Ielamrani]

I am back again. I am able to see the query result for about 10 seconds and then i get this message:

Data type mismatch in cretiria expression.

I can't copy the query or export it.

Where is the problem?

Thanks

 

[SkipVought]

Please post your SQL.

Skip,

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

 

[Ielamrani]

Sorry to reply late but Problem solved.


Thanks

 

[SkipVought]

Wonderful!

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}