Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2004
    Posts
    77

    Unanswered: Strip Address Number

    --------------------------------------------------------------------------------

    If possible, I'd like to some help with a query that strips the address number from a Parcel Address and leaves only the street name left.

    Before:
    1234 Somewhere St.
    321 Crossing Meadows Dr.
    1234A N. 9th St.

    After:
    Somewhere St.
    Crossing Meadows Dr.
    N. 9th St.

    Thanks,
    SKK

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    If you reliably have a number, space and then street name every time, use the InStr function to locate the space and take everything to the right of it.

    You could use the functions directly in the query but I suggest writing several lines of code in a module as a function and reference that function in your query - easier to debug.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if your data always has a space preceeding the street name then you could use the split function, or failing that the instr and search for the first space character. You may have to test to ensure that the address is prefixed by a number, as not all addresses have a number and street name.

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    A little Regexp?
    Code:
    ' Module declaration section
    Private gre as object
    
    ' Function
    public function rvsNoNums(Byval v_strIn as String) as string
        if (gre is nothing) then
            set gre = createobject("vbscript.regexp")
        end if
        gre.pattern = "^\d+([a-zA-Z] | )"
        with gre
            if .test(v_strIn) then
                rvsNoNums = .replace(v_strIn, vbNullString)
            else
                rvsNoNums = v_strIn
            end if
        end with
    end function
    Roy-Vidar

  5. #5
    Join Date
    Jan 2004
    Posts
    77
    Thanks all. Will try later - boss just gave me another pressing assignment, but will return...

    Thanks again,
    SKK

  6. #6
    Join Date
    Jan 2004
    Posts
    77
    Hey all...
    I think I got it. I have an update query:

    Right([Par_Addr],(Len([Par_Addr])-InStr([Par_Addr]," ")))

    It may not be correct but it appears to be doing what I want.
    Thanks again,
    SKK

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    That should work
    providing you have a space between the number and the street
    ie
    124 anywhere street
    NOT
    124anywhere street

    AND you don't have any awkward addresses such as
    myhousename anywhere street
    OR
    Flat 5 124 anywhere street

  8. #8
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    As stated by healdem, that expression will remove all characters from the start of the field till the first space, regardless of whether it contains digits or letters.

    Here's a small pattern change for the Regexp suggestion to allow for missing space

    gre.pattern = "^\d+( ||[a-zA-Z])( |)"

    One could probably also deal with "awkward" addresses too, as long as they have a recognizable pattern.
    Roy-Vidar

  9. #9
    Join Date
    Dec 2004
    Location
    Tennessee
    Posts
    31

    Question How do I call rsvNoNUms?

    I've made a module of the code that Roy suggested...now my question is where and how to call this function within my Sql Query statement?


    Thanks,

    Gene~
    The older I get, the more new stuff I tend to forget, the stuff I don't use any more...now that I remember!

    Mail Me

  10. #10
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    As you would use any function in a query, for instance like this in the field row if you only wish to display it

    MyCalculatedField: rvsNoNums(MyField)

    in sql

    select rvsNoNums(MyField) as MyCalculatedField ...

    Note however that if you're having Nulls in the field, you will probably need to both pass and return a Variant, possibly some testing within too.
    Roy-Vidar

  11. #11
    Join Date
    Dec 2004
    Location
    Tennessee
    Posts
    31

    Exclamation still won't work

    Roy,
    I did what you suggested and still got an error message. I've attached a gif so that you can see what I've done.


    Thanks,

    Gene~
    Attached Thumbnails Attached Thumbnails error_rvsNoNums.gif  
    The older I get, the more new stuff I tend to forget, the stuff I don't use any more...now that I remember!

    Mail Me

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so where have your defined your function "rvsNoNums", is it in a module, and was it declard as public within that module?

  13. #13
    Join Date
    Dec 2004
    Location
    Tennessee
    Posts
    31

    Strip Address Number

    Healdem,
    Yeah its in a module and declared public! Here's my code:

    Code:
    ' Module declaration section
    Dim gre As Object
    
    ' Function
    Public Function rvsNoNums(ByVal v_strIn As String) As String
        If (gre Is Nothing) Then
            Set gre = CreateObject("vbscript.regexp")
        End If
        gre.Pattern = "^\d+([a-zA-Z] | )"
        With gre
            If .test(v_strIn) Then
                rvsNoNums = .Replace(v_strIn, vbNullString)
            Else
                rvsNoNums = v_strIn
            End If
        End With
    End Function
    Thanks,


    Gene~
    The older I get, the more new stuff I tend to forget, the stuff I don't use any more...now that I remember!

    Mail Me

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    First off I'd want to check the function works: does the function work in forms or the debug window. It may also be worthwhile doing a compile of the whole app. I've seen somehting similar if there was a compliation error in another unrealted module.

    I'm just a little suspicious as to whether you can shell out to to the command object in a query, definately in a form or report.

    I know JET & Access can do some wierd tricks, and you don't know whether its JET or Access that is doing the dirty deed.

    BTW unless you declare gre as static, its my undestanding that it will always be nothing as the function is stateless. its possible you may have resource problem, not sure how well VBA tidies up after a call to create an object
    Last edited by healdem; 01-04-06 at 05:27.

  15. #15
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I still recommend declaring the object using the private keyword, but then also do not name the module the same as functions/subs (prefix modules for instance "bas"?). It is the latter causing the error, I think.
    Roy-Vidar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •