Making 2 fields from one

[JJT]

I have a table with a name field which contains the full name of a customer. I need to separate the name field into first name and last name fields. Any suggetions on a query that wil do that for me?

The full name field looks like this: Joe Smith

Thanks...much.

 

[jfgambit]

What does the Name look like?

Smith, John
John Smith
JohnSmith
SmithJohn
etc....

Let us know so that we can provide you with the correct code.

Thanks.

 

[FoFa]

If the name is delimited (say by a comma) than you could try
SELECT LEFT([NAME],INSTR([NAME],',')-1) as LastName,
RIGHT([NAME],INSTR([NAME],',')-LEN([NAME])) as FirstName
FROM MyTable

BUT you will find that will not be 100% depending on your data.

 

[Rob.Mills]

You need to use 2 separate functions to accomplish that.

The following function will generate the first name. You need to call it in the query like this:

fFirst("[Name of Name field]")

Public Function fFirst(str As String) As String

Dim i As Integer

For i=1 to 50 'Use whatever final number you want
If Asc(Mid(str,i,1)=32 Then 'This is the first space
fFirst=Left(str,i-1)
Exit Function
End If
Next i

End Function


This will create the last name:

Public Function fLast(str As String) As String

dim i As Integer

For i = 1 to 50
If Asc(Mid(str,i,1)=32 Then
fLast=Mid(str,i+1)
Exit Function
End if
Next i

End Function