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

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

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

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.


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.

OK that makes sense now. Thank you so much, it works perfectly!