Split data to 2 new fields

[Dimitris]

Hello in a table I have a field (F1) in which we have first and last name
entered together . For Example JOHN SMITH . I created 2 new fields one
called FNAME and one LNAME how can I have the first name (John) in field
FNAME and the last name (Smith) in the field LNAME.
Thanks for your help
Dimitris

 

[Keith]

Hello in a table I have a field (F1) in which we have first and last name
entered together . For Example JOHN SMITH . I created 2 new fields one
called FNAME and one LNAME how can I have the first name (John) in field
FNAME and the last name (Smith) in the field LNAME.
Thanks for your help
Dimitris

Put the following code into a module and then call each from a query,
passing F1 to both:

Public Function libFirstName(strName As String) As String

Dim lngLength As Long
lngLength = InStr(1, strName, " ") - 1
libFirstName = Left(strName, lngLength)

End Function

Public Function libLastName(strName As String) As String

Dim lngLength As Long, lngStart As Long
lngStart = InStr(1, strName, " ")
lngLength = Len(strName) - lngStart
libLastName = Right(strName, lngLength)

End Function

You'd need to handle nulls, probably in the query using an in-line "If":

FirstName: IIf(IsNull([F1]),Null,libFirstName([F1]))
LastName: IIf(IsNull([F1]),Null,libLastName([F1]))

You can then change the query type to update FNAME and LNAME.

HTH - Keith.
www.keithwilby.com

 

[Dimitris]

Thanks for your answer Keith.
I am sorry but I don't understand where exactly to write the code.
I am new in Access so I am confused.
Where exactly do I write the code? Where should I paste it? And should I
paste everything you wrote?
I appreciate your help.
Dimitris

Hello in a table I have a field (F1) in which we have first and last name
entered together . For Example JOHN SMITH . I created 2 new fields one
called FNAME and one LNAME how can I have the first name (John) in field
FNAME and the last name (Smith) in the field LNAME.
Thanks for your help
Dimitris

Put the following code into a module and then call each from a query,
passing F1 to both:

Public Function libFirstName(strName As String) As String

Dim lngLength As Long
lngLength = InStr(1, strName, " ") - 1
libFirstName = Left(strName, lngLength)

End Function

Public Function libLastName(strName As String) As String

Dim lngLength As Long, lngStart As Long
lngStart = InStr(1, strName, " ")
lngLength = Len(strName) - lngStart
libLastName = Right(strName, lngLength)

End Function

You'd need to handle nulls, probably in the query using an in-line "If":

FirstName: IIf(IsNull([F1]),Null,libFirstName([F1]))
LastName: IIf(IsNull([F1]),Null,libLastName([F1]))

You can then change the query type to update FNAME and LNAME.

HTH - Keith.
www.keithwilby.com

 

[Keith]

Thanks for your answer Keith.
I am sorry but I don't understand where exactly to write the code.
I am new in Access so I am confused.
Where exactly do I write the code? Where should I paste it? And should I
paste everything you wrote?
I appreciate your help.
Dimitris

Copy everything between the *** code *** delimeters. Go to the database
window and select the "modules" tab. Click "new" and you should get a
window with "Option Compare Database" in it. Below this line type "Option
Explicit" then below that paste the code. From the "debug" menu, select
"complile", then save the module as "Module1". Close the module.

Post back if you need more help.

Regards,
Keith.
www.keithwilby.com

 

[Klatuu]

There is a simpler method; however, be aware that no matter how you go about
it, there will be some that will not convert as expected. Here is a simpler
version of how to do this:

Sub SplitName(strFullName as String)
Dim varNames as Variant

varNames = Split(strFullName, " ")
rst![FNAME] = varNames(0)
rst![LNAME] = varNames(0)
End Sub

Hello in a table I have a field (F1) in which we have first and last name
entered together . For Example JOHN SMITH . I created 2 new fields one
called FNAME and one LNAME how can I have the first name (John) in field
FNAME and the last name (Smith) in the field LNAME.
Thanks for your help
Dimitris

Put the following code into a module and then call each from a query,
passing F1 to both:

Public Function libFirstName(strName As String) As String

Dim lngLength As Long
lngLength = InStr(1, strName, " ") - 1
libFirstName = Left(strName, lngLength)

End Function

Public Function libLastName(strName As String) As String

Dim lngLength As Long, lngStart As Long
lngStart = InStr(1, strName, " ")
lngLength = Len(strName) - lngStart
libLastName = Right(strName, lngLength)

End Function

You'd need to handle nulls, probably in the query using an in-line "If":

FirstName: IIf(IsNull([F1]),Null,libFirstName([F1]))
LastName: IIf(IsNull([F1]),Null,libLastName([F1]))

You can then change the query type to update FNAME and LNAME.

HTH - Keith.
www.keithwilby.com

 

[Keith]

You know, I've been programming Access for 10 years and have never come
across the Split function.

Sub SplitName(strFullName as String)
Dim varNames as Variant

varNames = Split(strFullName, " ")

****How does this bit work, surely you'll end up with FNAME and LNAME being
the same?****

rst![FNAME] = varNames(0)
rst![LNAME] = varNames(0) ***************************

End Sub

 

[Klatuu]

I've been doing Access for 9 yrs and other forms of basic for over 20 and
only found it about 6 months ago. There is also a Join Function that does
the exact opposite. It concatenates the elements of an array into a string.

You know, I've been programming Access for 10 years and have never come
across the Split function.

Sub SplitName(strFullName as String)
Dim varNames as Variant

varNames = Split(strFullName, " ")

****How does this bit work, surely you'll end up with FNAME and LNAME being
the same?****

rst![FNAME] = varNames(0)
rst![LNAME] = varNames(0) ***************************

End Sub

 

[Klatuu]

rst![FNAME] = varNames(0)
rst![LNAME] = varNames(0)
Typo, should be:
rst![FNAME] = varNames(0)
rst![LNAME] = varNames(1)

Ain't copy/paste wonderful

You know, I've been programming Access for 10 years and have never come
across the Split function.

Sub SplitName(strFullName as String)
Dim varNames as Variant

varNames = Split(strFullName, " ")

****How does this bit work, surely you'll end up with FNAME and LNAME being
the same?****

rst![FNAME] = varNames(0)
rst![LNAME] = varNames(0) ***************************

End Sub

 

[Keith]

I've been doing Access for 9 yrs and other forms of basic for over 20 and
only found it about 6 months ago. There is also a Join Function that does
the exact opposite. It concatenates the elements of an array into a
string.

Thanks for the info, that will come in useful )

 

[Keith]

rst![FNAME] = varNames(0)
rst![LNAME] = varNames(1)

Ain't copy/paste wonderful

Ah, so now my code simplifies to:

Public Function libFirstName(strName As String) As String

Dim varFirstName As Variant
varFirstName = Split(strName)
libFirstName = varFirstName(0)

End Function

Public Function libLastName(strName As String) As String

Dim varLastName As Variant
varLastName = Split(strName)
libLastName = varLastName(1)

End Function