General discussion

Locked

Extract a text string – Access 97

By chainsawz ·
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?!

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Extract a text string – Access 97

by DKlippert In reply to Extract a text string – A ...

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

Collapse -

Extract a text string – Access 97

by chainsawz In reply to Extract a text string &#8 ...

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!

Collapse -

Extract a text string – Access 97

by chainsawz In reply to Extract a text string – A ...

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.

Collapse -

Extract a text string – Access 97

by chainsawz In reply to Extract a text string – A ...

This question was closed by the author

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums