Parse First Name from Name Field

[Guest]

I have a name field that contains first names or first and last names
together. I want to show only the first name. In a report on the field I
tried setting the Control Source =Left([Name],InStrRev([Name]," ")-1) but it
returned a #Error if there is not a last name (first name only). If there
was a first and last name in the field it returned the first name only which
is what I want. How do I get first names only for the field when in contains
both first and first and last names?

Thanks,

Scott

 

[Douglas J. Steele]

=IIf(InStr([Name]," ") > 0, Left([Name],InStrRev([Name]," ")-1), [Name])

 

[fredg]

I have a name field that contains first names or first and last names
together. I want to show only the first name. In a report on the field I
tried setting the Control Source =Left([Name],InStrRev([Name]," ")-1) but it
returned a #Error if there is not a last name (first name only). If there
was a first and last name in the field it returned the first name only which
is what I want. How do I get first names only for the field when in contains
both first and first and last names?

Thanks,

Scott

If all you want is the first name, you could use:
=IIf(InStr([NameField]," ")>0, Left([NameField],InStr([NameField],"
")-1),[NameField])

However, this will return an incorrect first name if someone's First
Name includes a space, i.e. Mary Lou, which many consider as one name.

A Field should only contain one bit of data, either the First Name or
the Last Name, but not both. I would suggest you fix this sooner
rather than later, as this will cause you additional problems in the
future. It's quite simple to combine the two fields; much more
difficult to separate one field into it's 2 or more components.

 

[Guest]

Hi Scott,

I recommend splitting this data into two fields, so that you do not need to
run time-consuming functions each time you run your report. One of my
favorite utilities for splitting names in Access is called "Splitter for
Microsoft Access". If you don't mind popping $39 for a copy, you will have a
very good utility that can untangle the messiest name data:

http://www.infoplan.com.au/splitter/

A very easy method to use involves exporting the table to Excel. From within
Excel, use Data > Text to Columns... Then re-import your table back into
Access.

You can also create the new fields in the table and then use an update query
to populate the new fields. The update query would look like this:

Field: FirstName
Table: Your tablename
Update To: SplitName([FullName],0)

Field: LastName
Table: Your tablename
Update To: SplitName([FullName],1)

If you do not wish to permanently split this name data, then create a query
that includes fields similar to this, plus all the other fields that you need
in your report's recordsource:

Field: FirstName: SplitName([FullName],0)
Table: Your tablename

Field: LastName: SplitName([FullName],1)
Table: Your tablename

Add the following function to a new standard module. Name the module
something like basSplitNames (the name of the module *must* be different than
the name of any procedures in your application). Copy and paste the following
function into it. This will create a zero-based array named strResult. The On
Error Resume Next statement will prevent the function from choking to a halt
if there is no middle name for a given record.

Public Function SplitName(FullName As String, _
intElement As Integer) As String
On Error Resume Next

Dim strResult() As String

strResult = Split([FullName], Chr(32))
SplitName = strResult(intElement)

End Function



Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

Exactly the result I needed, thank you!

=IIf(InStr([Name]," ") > 0, Left([Name],InStrRev([Name]," ")-1), [Name])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a name field that contains first names or first and last names
together. I want to show only the first name. In a report on the field I
tried setting the Control Source =Left([Name],InStrRev([Name]," ")-1) but
it
returned a #Error if there is not a last name (first name only). If there
was a first and last name in the field it returned the first name only
which
is what I want. How do I get first names only for the field when in
contains
both first and first and last names?

Thanks,

Scott

 

[Guest]

Exactly the result I needed, thank you!

I have a name field that contains first names or first and last names
together. I want to show only the first name. In a report on the field I
tried setting the Control Source =Left([Name],InStrRev([Name]," ")-1) but it
returned a #Error if there is not a last name (first name only). If there
was a first and last name in the field it returned the first name only which
is what I want. How do I get first names only for the field when in contains
both first and first and last names?

Thanks,

Scott

If all you want is the first name, you could use:
=IIf(InStr([NameField]," ")>0, Left([NameField],InStr([NameField],"
")-1),[NameField])

However, this will return an incorrect first name if someone's First
Name includes a space, i.e. Mary Lou, which many consider as one name.

A Field should only contain one bit of data, either the First Name or
the Last Name, but not both. I would suggest you fix this sooner
rather than later, as this will cause you additional problems in the
future. It's quite simple to combine the two fields; much more
difficult to separate one field into it's 2 or more components.

 

[Guest]

Thank you for your response. I agree it would be preffered to have two
fields; however, I am using ODBC to connect to a retail SQL database so in
this instance the two fields are not a possiblity.

Scott

Hi Scott,

I recommend splitting this data into two fields, so that you do not need to
run time-consuming functions each time you run your report. One of my
favorite utilities for splitting names in Access is called "Splitter for
Microsoft Access". If you don't mind popping $39 for a copy, you will have a
very good utility that can untangle the messiest name data:

http://www.infoplan.com.au/splitter/

A very easy method to use involves exporting the table to Excel. From within
Excel, use Data > Text to Columns... Then re-import your table back into
Access.

You can also create the new fields in the table and then use an update query
to populate the new fields. The update query would look like this:

Field: FirstName
Table: Your tablename
Update To: SplitName([FullName],0)

Field: LastName
Table: Your tablename
Update To: SplitName([FullName],1)

If you do not wish to permanently split this name data, then create a query
that includes fields similar to this, plus all the other fields that you need
in your report's recordsource:

Field: FirstName: SplitName([FullName],0)
Table: Your tablename

Field: LastName: SplitName([FullName],1)
Table: Your tablename

Add the following function to a new standard module. Name the module
something like basSplitNames (the name of the module *must* be different than
the name of any procedures in your application). Copy and paste the following
function into it. This will create a zero-based array named strResult. The On
Error Resume Next statement will prevent the function from choking to a halt
if there is no middle name for a given record.

Public Function SplitName(FullName As String, _
intElement As Integer) As String
On Error Resume Next

Dim strResult() As String

strResult = Split([FullName], Chr(32))
SplitName = strResult(intElement)

End Function



Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have a name field that contains first names or first and last names
together. I want to show only the first name. In a report on the field I
tried setting the Control Source =Left([Name],InStrRev([Name]," ")-1) but it
returned a #Error if there is not a last name (first name only). If there
was a first and last name in the field it returned the first name only which
is what I want. How do I get first names only for the field when in contains
both first and first and last names?

Thanks,

Scott