Extract Middle name 1

[jlg5454]

Is there a function in an Access Query that extracts the Middle name or Middle intitial from a field? Once it is extracted the new field will just read first and last name.

Thanks for any assistance possible.

 

[markgrizzle]

You could create your own function and include that in your query.

Your function would use the instr function and two integer variables. Use instr to find the first blank in the field, and store the position in variable 1.

Use instr again (and variable 1 as the startpoint) and search for the next blank space. Store that position in variable 2.

Use both variables with the mid function as the return value for your function.

 

[crobg]

Without writing your function:

NameWithoutMiddle: Left([strName],InStr([strName]," ")) & " " & Right([strName],InStrRev([strName], " "))

Though I do like writing custom functions.

 

[jlg5454]

Crobg,
I tried your expression, but it does not recognize InStrrev? Any Ideas.

Thanks

 

[crobg]

What version are you using? I have 2003 and it is there.

Since you do have instr, modify the right statement to:

Right([strName],Len([strName])-InStr(InStr([strName]," "),[strName] " "))

Gets a little long but should do the trick as long as there is always a middle name or initial.

 

[TomCologne]

ACC2000: Cannot Use New Visual Basic for Application Functions as Expressions

http://support.microsoft.com/default.aspx?scid=kb;en-us;225956

Sample expressions to extract portion of text string in Access 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;209045

TomCologne

 

[crobg]

Tom
That is fixed in 2003. That link for data extract is good info too. Here's a star.

 

[Eupher]

And here's an FAQ with VBA functions to emulate Access2K functions in earlier versions (including InStrRev): faq705-4342

Ken S.

 

[alvechurchdata]

Gets a little long but should do the trick as long as there is always a middle name or initial.

Gets even longer but you can use the Split() function to cope with one, two and three-word names. "Split" splits a string into separate words and puts them in an array.

 Public Function SplitWord(strName) As String
 aNames = Split(strName)
 Select Case UBound(aNames)
   Case -1
     '-- No word
     SplitWord = "No name"
   Case 0
     '-- One name - print it
     SplitWord = aNames(0)
   Case 1
     '-- Two names - print the first
     SplitWord = aNames(0)
   Case Else
     '-- Three or more names - print the second
     SplitWord = aNames(1)
 End Select
 
 End Function

Geoff Franklin

http://www.alvechurchdata.co.uk