You may not want to use Right() if your field has 'irregular' data like  
your first one above.  You should use the Mid() function.  Does your third  
data segment always begin at the 13th position ... or is the best you can  
say that it is always after the second semi-colon?

Before you answer that, ask yourself, is there any way to NOT put this  
data in the same field to begin with?  It looks like it should be in three  
separate fields.  Second, is there any way to eliminate irregular data,  
which would certainly make it easier to extract.

Darryl Kerkeslager

jliccia...@mssystems.com wrote:

Hope this doesn't have to be fast... I could do it with strReverse and
then snip off the first semi-colon and then take everything between
position 1 and the next semi-colon, but that was way too much of a
headache.  Will this work for ya?

Option Compare Database

Public Function LastChunkOfString(ByVal strText As String, ByVal
strDelim As String) As Variant
'Sample inputs and outputs... note, these are TEXT.
' 13981;38355;563550;
'returns: 563550
' 13767;38355;520270-1;
'returns: 520270-1
' 44795;38355;110818;
'returns: 110818

   Dim ChunkString As Variant
   ChunkString = Split(strText, strDelim, , vbTextCompare)
   LastChunkOfString = ChunkString(UBound(ChunkString) - 1)

End Function

so I _guess_ you could do it in SQL...

SELECT... ChunkString(UBound(Split(strText, strDelim, , vbTextCompare))
- 1)
FROM...
WHERE...

but I didn't mess with that... that's the fun you get to have!

BTW, I agree with Darryl - the best way out of this mess in the future
is to not get yourself into it in the first place.  Break up your data
as small as you can BEFORE you enter it, and you won't have to do these
things to begin with.