[AccessD] Splitting information from One field into Two anddumpingthe middle

Thanks Stuart and Andy Lacey for the answer!
They both work great...

Philip
:)


-----Original Message-----
From: Andy Lacey [mailto:andy at minstersystems.co.uk] 
Sent: Sunday, December 12, 2004 2:14 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Splitting information from One field into Two
anddumpingthe middle

Hi Philip

Here's a function which will strip any string which has separators into its
individual elements and put them in an array. All you have to do is pass it
your string, the name of the array which you must already have dim'd, and
then on return you can take the first and last elements of the array.

So

Dim astrNames() as string
Dim intEls as integer

Call Parse(inputstring, astrNames, ":",True)

then

strFirstName=astrNames(1)
strLlastname=astrNames(ubound(astrnames))

(add your own processing to cater for empty string, or just one element)


-------------the function


Function Parse(prmStr as String, prmArr, prmDelim as String, Optional
prmTrim)
'Takes a string within which is a list separated by a specified delimiter
(eg commas)
'Pass also an initialised string array and the delimiter
'Finally optional parameter to denote whether to trim the results (useful if
string is
'comma-separated with spaces after the commas)

'Fills the array with individual elements from the string

Dim intX As Integer
Dim intPointer As Integer

If IsMissing(prmTrim) Then
  prmTrim = False
End If
If Nz(prmStr, "") <> "" Then
  intX = 1
  'Loop thru string looking for delimiter
  Do While InStr(intX, prmStr, prmDelim) > 0
    intPointer = intPointer + 1
    ReDim Preserve prmArr(intPointer)
    prmArr(intPointer) = Mid(prmStr, intX, InStr(intX, prmStr, prmDelim) -
intX)
    If prmTrim Then
      prmArr(intPointer) = Trim(prmArr(intPointer))
    End If
    intX = InStr(intX, prmStr, prmDelim) + 1
  Loop
  'At end do the last element when no more delimiters
  If Trim(Mid(prmStr, intX)) <> "" Then   'Not if delimiter at end
    intPointer = intPointer + 1
    ReDim Preserve prmArr(intPointer)
    prmArr(intPointer) = Mid(prmStr, intX)
    If prmTrim Then
      prmArr(intPointer) = Trim(prmArr(intPointer))
    End If
  End If
End If

End Function

HTH

-- Andy Lacey
http://www.minstersystems.co.uk 

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Technical Designs
> Sent: 12 December 2004 01:17
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Splitting information from One field into 
> Two and dumpingthe 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.
> 
>  
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>