Extract Text from Field

  • Thread starter Thread starter Salemgrad
  • Start date Start date
S

Salemgrad

I have a field which has names imbedded in the field. There is no standard
format in the place the info is located nor is there a standard format to the
name (ie. C_Smith, C Smith, etc.). I assume I have to nest several
MidFunctions inside an IIF statement but not sure how to account for all the
possibilities. Also, I cannot get the full name to pull when it ihas a space
(C Smith) followed by a space. Any sugesstions? Here is an example of the
data:

AMERICAS\BETH_KIEFFER;AMERICAS\FERNANDO_M_LOPEZ;
Kelly Huff / Melanie Bolke - 9/23/2005 3:33:52 PM
AMY_DANEK - 3/13/2006 1:58:34 PM
MARIA_HENNECKE - 6/2/2006 1:48:48 PM
AMERICAS\LAURA_DIMOV;AMERICAS\PEDRO_PAZMINO
Group Migrated from Netware - Mariely_Franzetti
 
What you are trying to do is virtually impossible. Parsing names or
addresses is an exercise in futility and frustration.
As you have notices, there are too many possibilities and variations for
this to work even at a low percentage hit rate.

Suggestion - Don't even try. Put the whole thing in a memo field and assign
a human to do the job.
 
This will take some fairly sophisticated programming to accomplish and even
then I would venture to say the results would be problematic.

I would not try this in a query without writing a VBA function. And it gets
particularly tough in that you can have more than one name in the field data.

As noted elsewhere, your best bet may be to use human brain power to solve the
problem - one record at a time.

If you have a lot of records:
-- Identify the patterns
\Name; = Name
Name / Name - date = two names
Name - date = one name
text string - Name = one name
-- Process each field using the patterns to extract the name(s)
Once you have names identified using a pattern then skip other pattern
processing
-- Return a string with all the names separated by a specific character(s)
-- Decide how you want to handle multiple names

Here is a rough outline of what I mean
Public Function fGetPeople(strIn)
Dim strOut as String
Dim aItems as Variant
Dim iLoop as Long

If Len(Trim(StrIn & ""))=0 Then
fGetPeople = Null

'handle | name semicolon pattern
ElseIf StrIn Like "*[\]?*;*" Then
aItems = Split(StrIN & ";","\")
For iLoop = Lbound(aItems) to UBound(aItems)
If aItems(iLoop) Like "*;" Then
strOut = ";" & strOut & _
Left(aItems(iLoop),Instr(1,aItems(iLoop),";")-1)
End If
fGetPeople = Mid(strOut,2)
next iLoop

'handle name slash name dash date pattern
ElseIf ...

End if

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top