Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: Searching for last instance of a substring and copying what follows

    Hi all,

    I am rather new to the use of SQL and Access, and am not sure how to go about solving this problem. I am using Access 2007 and need to search for the last address listed in a field called FORMERADDR. Multiple addresses are listed in each cell, separated by ';'. Therefore I would need to find the last instance of ';' and copy the string that follows, then insert that address into another field. I think that this can be done with some combination of the right and instr functions, but can not seem to figure it out. Thank you all in advance for the help.


    Brad

  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    I'm sure there's an easier way, but, here's what pops into my brain first:

    dim x as integer
    dim CharLoc as byte
    dim LastAddress as String

    for x = 1 to len(FormerAddr)
    if mid(formeraddr, x,1) = ";" then
    CharLoc = x
    end if
    next x

    lastAddress = trim(mid(FormerAddr, CharLoc + 1, (len(FormrAddr - CharLoc)))

    Certainly not elegant, but, should get the job done

    Sam
    Last edited by SCrandall; 05-25-10 at 16:17. Reason: Made a whoops
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by blingenf View Post
    Hi all,

    I am rather new to the use of SQL and Access, and am not sure how to go about solving this problem. I am using Access 2007 and need to search for the last address listed in a field called FORMERADDR. Multiple addresses are listed in each cell, separated by ';'. Therefore I would need to find the last instance of ';' and copy the string that follows, then insert that address into another field. I think that this can be done with some combination of the right and instr functions, but can not seem to figure it out. Thank you all in advance for the help.


    Brad
    Brad,

    The trick is to use InStrRev()

    ? Mid("Address1;Address2;Addres3",InStrRev("Address1 ;Address2;Addres3",";")+1)

    Addres3


    To use Sam's code, I would probably create a custom function.

    Sam's example can be done with this single line:

    Function GetLastAddress(pFormerAddr as string) as string

    GetLastAddress = trim(mid(pFormerAddr ,InStrRev(pFormerAddr ,";")+1 ))

    End function
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Join Date
    May 2010
    Posts
    3
    Thank you guys, those both seem like they should work. My problem is that I do not know VBA (it is on my list of things to do), but I am familiar with other languages, such as java and C, so these solutions do make sense. I am wondering if this can be done purely in SQL though, since that is how I have been handling the project. Or will I just have to play around with VBA some and figure it out? Thanks again for your responses.

  5. #5
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by blingenf View Post
    Thank you guys, those both seem like they should work. My problem is that I do not know VBA (it is on my list of things to do), but I am familiar with other languages, such as java and C, so these solutions do make sense. I am wondering if this can be done purely in SQL though, since that is how I have been handling the project. Or will I just have to play around with VBA some and figure it out? Thanks again for your responses.
    It can be done in a query as a calculated field.

    Code:
    Select ... trim(mid([FORMERADDR] ,InStrRev([FORMERADDR] ,";")+1 )) as LastAddr ...

    I would urge you to reconsider using a single memo field in this way. I find it very difficult to work with and prone to data lose/corruption.

    I find it a better solution to use a related table for address history with a separate record for each address. I like to use additional fields for date/time etc. It also allows you to avoid using a memo field.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  6. #6
    Join Date
    May 2010
    Posts
    3
    OK that makes sense now. Thank you so much, it works perfectly!

Tags for this Thread

Posting Permissions

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