Get the last entry in a text field

[MartyO]

Hi there,
I'm sure some combination of the Split Function and InStr function will work
here but I just can't get the combo right.
I have a field that is populated by a survey, so multiple entries are put in
one field separated by a ; and a space, like this "All Instructional Staff;
All Employees; K-12"
and so on. Each record could have a different amount of entries in that
field.
I need to grab the last entry, put it in a mem variable, and then write a
record in another table with that content.

How do I grad that last of the text following the last ( in that field?
Thanks in advance!
Marty
Can anyone give me some direction.

 

[Jeff Boyce]

Marty

One (pretty convoluted) way would be to reverse the string, look for the
"first" ";", take everything up to there, re-reverse what you took.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

AN Untested VBA function - typed on the fly

You would call it with
FGetLastItem([FieldName],";"

Public Function fGetLastItem( strIN, sDelim as String)
Dim A as Variant
IF len(strIN & vbNullstring)= 0 then
fGetLastItem = strIn
ELSE
A = Split(strIn,sDelim)
fGetLastItem = a(Ubound(a))
END IF
End Function

Or use just use instrRev
Trim(Mid(SomeField,InstrRev(SomeField,";")+1))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County