Picking out words in a text field

[CD Tom]

I have a text field that contains names and numbers separated by a coma. Ex
(2031 Mary Jane Smith, Jim Anderson, Bill Wright) what I'd like to do is find
Mary and then look her up in the student master file (there will be only one
Mary Jane Smith in the file), when I've done that I want to move to the next
person in the list and find him in the student master. What's the best way
to search through this field and get each person out.

Thanks for all your help.
Tom

 

[Klatuu]

That is an almost impossible task, Tom. Name matching and Address matching
are never going to be 100% accurate. There are too many variables. You have
already encountered one. How do we know Mary Jane Smith is Mary Smith in a
table field? Sure, you can do some string manipulation to take the middle
name out and search by first and last, but then you have the problem of
people who have multiple middle names (George Herbert Walker Bush) and those
who have either first are last names that have a space in them (Rip van
Winkle, John Wilkes Booth Jr). And, we haven't even discussed data entry
errors.

But, as a strart, use the Split function to create an array of the names you
will try match and loop throught the resultant array using DLookup to try to
find them.

Best of luck.

 

[CD Tom]

Not to sure about the split function? Couldn't I search for the end of the
string and then work backward until I found the coma and that would be one
person, I know for a fact that each name is separated by a coma.

 

[Klatuu]

Split function is less work.

Dim varNameList As Variant
Dim lngX as Long

varNameList = Split(txtAllName, ",")
For lngX = 0 To Unbound(varNameList)
'Here is where you try to find the name
Next lngX

 

[CD Tom]

I'll give that a try and let you know. Thanks for all your help.

 

[CD Tom]

In the statement For LngX t= 0 to Unbound(varNameList) what is the Unbound?

 

[Beetle]

That should be UBound, not Unbound. UBound represents the largest
available value in the array

 

[Klatuu]

Ubound misspelled