How do I Split My [Name] field into [FirstName] and [LastName] ?

[k94ever]

I have 1 field that contains the names, ex: "John Brown", that field name is
[Name], I want to split the [Name] field into 2 fields [FirstName] and [LastName].

How can I do this in an update query? I am not real sure how to use the update query as I have never had to before.

Thanks in Advance ...

 

[Banana]

Take a look at VBA function "Split"; you can find more about it in Access help files or searching on the board.

BTW, you can use VBA functions within queries, FYI.

 

[DCrake]

Here are two simple functions that will extract either the surname or forename from the fulll name

Public Function ExtractFirstName(AnyName As String) As String

Dim nSpace As Integer

AnyName = Trim(AnyName)

nSpace = InStr(AnyName," ")

'One word in string
If nSpace = 0 Then
   ExtractFirstName = AnyName
   Exit Function
End If

ExtractFirstName = Left(AnyName,nSpace-1)

End Function

Public Function ExtractLastName(AnyName As String) As String

Dim nSpace As Integer

AnyName = Trim(AnyName)

nSpace = InStrRev(AnyName," ")

'One word in string
If nSpace = 0 Then
   ExtractLastName = AnyName
   Exit Function
End If

ExtractLastName = Mid(AnyName,nSpace+1)

End Function

Create a query and add two columns

Fname:ExtractFirstName([FullName])

Lname:ExtractLastName([FullName])

That should get you both the first word in the string and the last word in the string. If there are no spaces in the string then it will return the whole string.

CodeMaster:

 

[burrcm]

Add the first name and last name fields to your table. Query the table, set the query to update then add the following as the first and last name update criteria.

First Name: Left([Name],InStr([Name]," ")-1)

Last Name: Right([Name],Len([Name])-InStrRev([Name]," "))

Chris B

 

[k94ever]

Thanks guys, that seems to have made the difference.

Now here is another question:
I'm wanting to use this query to Update the existing table using the Update Query, but it keeps asking me for atleast 1 destination field. I know what field I want it to update to but I guess I don't know the correct format to put in the "Update To:" field.

TIA

 

[burrcm]

Create a query containing First Name and Last Name, make it an update query and put my first name code in the criteria field of first name and the other below last name. Save and run the query and it will populate the columns in the table.

Chris B

 

[burrcm]

The criteria field is the update to line.

Chris B