Extracting the first name

[John]

Hi

How can I extract the first name from the full name where each name is
separated by spaces?

Thanks

Regards

 

[fredg]

Hi

How can I extract the first name from the full name where each name is
separated by spaces?

Thanks

Regards

Answered elsewhere.
Please do not multi-post. If you feel you must post the same question
to more than one newsgroup (and it's seldom necessary), cross-post by
adding each additional newsgroup in the To Newsgroups: box, separated
by a comma.
This way an answer in one newsgroup will be seen in each of the
others. More readers will see the response and learn, and less time
will be spent on duplicate answers.

See Netiquette at http://www.mvps.org/access

It's a great site to visit anyway.

 

[Guest]

I use functions that I first wrote in MS Basic for DOS, and have used it
mostly unchanged ever since, just updated to check for a Null String for
Access, and the Optional in the parameter list. It's a little elaborate for
just the first name, but it's good for getting any specific word.

You can use this in an Access query as a new column FirstName:
Word([Tablename].[Fullname],1," ")

Or:
LastName: Word([Tablename].[Fullname],Words([Tablename].[Fullname]," "), " ")

' ************************************************************************
Public Function Word(ByVal S1 As Variant, ByVal N As Integer, Optional Delim
As String = " ;,.") As Variant
' ************************************************************
' Return the Nth word from String
' Words are delimited by blanks, commas, semicolons and periods by default
' ************************************************************
Dim Pos As Integer, WC As Integer, R As String
If IsNull(S1) then
Word = S1
Exit Function
End If
Pos = 1: R = "": WC = 0
Do While Pos <= Len(S1) And (N > WC)
R = ""
Do While InStr(1, Delim, Mid(S1, Pos, 1), 1) > 0
Pos = Pos + 1
If Pos > Len(S1) Then Word = R: Exit Function
Loop
WC = WC + 1
Do While InStr(1, Delim, Mid(S1, Pos, 1), 1) = 0
R = R & Mid(S1, Pos, 1)
Pos = Pos + 1
If Pos > Len(S1) Then
Word = R
If N > WC Then Word = ""
Exit Function
End If
Loop
Loop
Word = R
End Function

' **********************************************************
Public Function Words(ByVal S1 As Variant, Optional Delim As String = "
;,.") As Integer
' **********************************************************
' Return the number of words in string
' Words are delimited by blanks, comma, semicolon and period by default
' **********************************************************
Dim Pos As Integer, NWords As Integer
If IsNull(S1) then
Words = 0
Exit Function
End If
Pos = 1: NWords = 0
Do While Pos <= Len(S1)
Do While InStr(1, Delim, Mid(S1, Pos, 1), 1) > 0
Pos = Pos + 1
If Pos > Len(S1) Then Words = NWords: Exit Function
Loop
NWords = NWords + 1
Do While InStr(1, Delim, Mid(S1, Pos, 1), 1) = 0
Pos = Pos + 1
If Pos > Len(S1) Then Words = NWords: Exit Function
Loop
Loop
Words = NWords
End Function