[AccessD] Splitting information from One field into Two and dumping the middle

Hi Group

 

I have been reading and learning lots from this list. and even coming up
with some of the same answers! .. once in a while.

This is my first question to the group.

 

I have one field [FullName] with .           Name:number
or

 
Name:subname:number              where there can be more than one recurrence
of subnames

 

What I would like to do is split "name" into [splitname] and "number" into
[splitnumber] fields of tblTable and loose the middle subnames (if there are
any)

 

I have

            Me.SplitName = Trim(Left(Trim(Me.FullName),
InStr(Trim(Me.FullName), ":") - 1))    '. this works fine

 

Me.SplitNumber = Trim(Right(Trim(Me.FullName), Len(Trim(Me.FullName)) -
(InStr(Trim(Me.FullName), ":") - 0)))

 

.. SplitNumber works fine if there is only one ":" in the string.. but there
might be 2 or more ":" sometimes

 

the Name:subname:number format is from an exported file being imported into
MSAccess.  The only consistent thing is the ":" 

 

Thanks in advance

 

Philip S.