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

 

[DotNetGnat]

are st and ave are the only two options that you want to check??

i dont think so..but just asking...you have to come up with some custom dirty functionality to achieve what you want to do...

-DNG

 

[Ielamrani]

Good question.

Here are some options:

RD should should be ROAD
St should be Street
BOULEVARD should be blvd
Ave should be AVENUE
LN should be Lane
DR should be Drive
E Should be EAST
W Should be WEST

These are just a few...
Thanks

 

[SkipVought]

First build a cross reference table like this
[tt]
Abbrev Full
Rd Road
Ave Avenue
St Street
S South
W West
E East
N North
Blvd Boulevard
Ln Lane
[/tt]

Function FullDesc(sVal As String)
    Dim a, i As Integer, r
    a = Split(sVal, " ")
    For i = 0 To UBound(a)
        For Each r In [Abbrev]
            If r.Value = a(i) Then
                a(i) = r.Offset(0, 1).Value
                Exit For
            End If
        Next
        FullDesc = FullDesc & a(i) & " "
    Next
    FullDesc = Left(FullDesc, Len(FullDesc) - 1)
End Function

Skip,

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

 

[Ielamrani]

I am about to leave work in a few minutes, I will try your idea Monday and I'll let you know. Thanks soooo much.

Ismail

 

[SkipVought]

Oops, I just realized that I gave my answer in Excel-eeze.

I'll convert it to Access-ezze.

Skip,

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

 

[Ielamrani]

I took a look at your code and I am not sure if I can do it by myself cause I am not an expert in vba.
If you do not mind me asking, after I create the table what do I do from there?
thanks
Ismail

 

[SkipVought]

You would paste the code into a module.

then in the Query Grid, in the Address field
[tt]
FullDesc([Address])
[/tt]
should return the full address AFTER I fix the code point to an ACCESS table.

Skip,

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

 

[alvechurchdata]

St Street

You'll have to build in some sort of exception handler to ensure that you don't convert "St James Sq" into "Street James Square".

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Ielamrani]

SkipVought, are you going to send me the code in Access? Thanks.
Ismail

 

[Ielamrani]

I created a table called Abrrev that contains the following:
Abbrev Full
Rd Road
Ave Avenue
St Street
S South
W West
E East
N North
Blvd Boulevard
Ln Lane

I tried running a query the way you said:FullDesc([Street]).
The query freezes and I am getting the following error message:

Compile Error: External name not defined.

I am not an expert in Vba, so please help.

Ismail

 

[SkipVought]

Function FullDesc(sVal As String)
    Dim a, i As Integer, rst As DAO.Recordset
    
    a = Split(sVal, " ")
    For i = 0 To UBound(a)
        
        Set rst = CurrentDb.OpenRecordset("Select Full From AddrAbbrev Where Abbrev='" & a(i) & "'", dbOpenSnapshot, dbReadOnly)
        
        On Error Resume Next
        rst.MoveFirst
        If Err.Number = 0 Then
            a(i) = rst(0)
        End If
        On Error GoTo 0
        
        FullDesc = FullDesc & a(i) & " "
    Next
    FullDesc = Left(FullDesc, Len(FullDesc) - 1)
End Function

Skip,

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

 

[SkipVought]

BTW,

you must ALSO set a reference in Tools/References to the Microsoft DAO n.m Object Library.

Skip,

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

 

[Ielamrani]

I am sorry to be a pain, but I am still getting an error message:

Runtime error'13'
type mismatch

thanks

 

[SkipVought]

And the error is occurring on WHAT statement?

How are you using this function.

Skip,

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

 

[Ielamrani]

Like I said I am not an expert in Vba.
This is what I did:

I created a table called AddrAbbrev with the following field:

abbrev Full

ST STREET
AVE AVENUE
RD ROAD
W WEST
E EAST
S SOUTH
N NORTH
BLVD BOULEVARD
LN LANE
BUILDING BLDG
TUPE TURNPIKE
ROOM RM
APARTMENT APT


I put the code you send me in a new module.
I created a query with the following sql code:

SELECT Address1.STREET, FullDesc([STREET]) AS Expr1
FROM Address1;

Address1 is the name of the table where the address changes is happening.

When I try to run the query I get the error in the code of the module on the following line:

Set rst = CurrentDb.OpenRecordset("Select Full From AddrAbbrev Where Abbrev='" & a(i) & "'", dbOpenSnapshot, dbReadOnly)


I hope this help to track the problem.
I really appreciate your help.

Ismail

 

[SkipVought]

Ismail,

It works perfectly in my database using it exactly as you have described.


What object library did you reference in Tools/References?


Skip,

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

 

[Ielamrani]

Object library:
Microsoft DAO 3.0 Object Library.

I really appreciate your help and patience.
Ismail

 

[SkipVought]

Hmmmmmmm...

Mine is based on the 3.6 Object Library. This may be where the problem lies.

You're going to have to figgure out the correct arguments for the OpenRecordset Method for 3.0.

Just as a longshot, try terminating the SQL string with a [semicolon]

        Set rst = CurrentDb.OpenRecordset("Select Full From AddrAbbrev Where Abbrev='" & a(i) & "';", dbOpenSnapshot, dbReadOnly)

Skip,

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

 

[Ielamrani]

that was it, it does exactly what I wanted it to do. 3.6 Object Library is the right object library. Man, you're good.

I have another question if you do not mind. In order to fix the addresses I need to run a query to identify them.

How do I run a query to get all the address containing the word Ave, St, Rd etc...

in the following Example

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

The query should give me only:

1787 New York Ave
35 Smith St

Again I really really appreciate your help. thanks

Ismail