Software

Ask something
Discussion Locked

Extract a text string – Access 97

0 helpful
4 replies

by chainsawz

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Extract a text string – Access 97

In Access 97, I have a name field, which contains first name and sometimes middle initial.
Example: John P.
Mary J.
George
Sue
I use this expression to separate the middle initial into another field:
Middle Initial: IIf(InStr(InStr([TODAY].[FIRST NM]," ")+1,[TODAY].[FIRST NM],"")<>1,Right([TODAY].[FIRST NM],Len([TODAY].[FIRST NM])-InStr(InStr([TODAY].[FIRST NM],"")+1,[TODAY].[FIRST NM]," ")),"")

I can’t seem to get this to work to create a separate field for the first name. Help?!

4 replies

by dklippert

Try one of theses
Left(([FirstName]),1) & IIF( InStr(1,[FirstName]," ")>0,Mid([FirstName],(InStr(1,[FirstName]," ")+1),1),"") & "."

Here?s an interesting site:

http://www.users.bigpond.com/wemba/splitter.htm

Or manipulate this macro:'Name Field [Name] may or may not have a middle initial
'In the first case search for "." as the name divider
'In the second case search for " " as the name divider

MsgBox Me![Name]
If InStr(2, Me![Name], ".") Then 'Is there a MI or not? FirstName = Left(Me![Name], InStr(2, Me![Name], ".") - 1)
LastName = Mid(Me![Name], InStr(2, Me![Name], ".") + 1)
Else
FirstName = Left(Me![Name], InStr(2, Me![Name], " ") - 1)
LastName = Mid(Me![Name], InStr(2, Me![Name], " ") + 1)
End If
MsgBox FirstName
MsgBox LastName

by chainsawz

Thanks for your help! The first two suggestions didn't work, so I will try the third. I'm not sure how to manipulate a macro in Access, but I will give it a shot!

by chainsawz

Thanks for your quick response! The first two suggestions aren't working to split the field, and I'm just not sure how to implement the third.

This discussion is closed to new replies.