Nedd help with LastName,FirstName Middle

[hhh1027]

I have table where full name is in one field and I would like to separate to three fields. Currently this is how it is and there is two ways.

FullName = Doe,John B and Doe,John

there is no space between the first name and last name only a coma and space between the first name and middle initial when available and I would like to have it this way

FirstName = John
LastName = Doe
MiddleIn = B when available.

I was told you can do it in function but I'm very new with access so I need help.

Thanks

 

[John Big Booty]

It should be reasonably straight forward to split your names that are in the format Doe,John by using Instr(), Left() and Right().

Names in the format Doe,John B will take a little more work but should still also be a achievable using the available VBA String functions

 

[John Big Booty]

The way I would tackle this would be to first identify if the name is in the format Doe,John B record the Middle name if applicable and then use Replace() to trim the string to Doe,John and then split that to first and last names.

 

[Mr. B]

I have attached a small database with the funnctionality you want. Take a look at the sample and when you have questions, just let me know.

If you are not familiar with VBA code you many need a little assistance with that part.

This sample database file has one table, one form and VBA code behind the form that includes the user-defined function that actually does the spliting of the name.

 

[jdraw]

I have table where full name is in one field and I would like to separate to three fields. Currently this is how it is and there is two ways.

FullName = Doe,John B and Doe,John

there is no space between the first name and last name only a coma and space between the first name and middle initial when available and I would like to have it this way

FirstName = John
LastName = Doe
MiddleIn = B when available.

I was told you can do it in function but I'm very new with access so I need help.

Thanks

Here's a function that you could use

'----------------------------------------------------------------------------------
' Procedure : ParseName
' Author    : Jack
' Created   : 2/23/2010
' Purpose   : To parse a field containing the person's full name and to return
' the first name, or the initial if it exists, or last name depending on the
' value of strWhich.
'
' NOTE: The format of the fullname field is
'       Lastname, Firstname Initial(may not be present)
' eg    a)De Jesus, Charlene K.
'        b)O'Sullivan, Margaret
'----------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: strname == the person's fullname
'         strWhich = F  First Name
'                  = M  Middle Initial
'                  = L  Last Name
'
' Sample call:    ParseName(rs!FullName, "F")  ' to get First Name
'
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function ParseName(strName As String, strWhich As String) As String
  
Dim strUtil As String
Dim strLastname As String
Dim strFirstname As String
Dim strMiddle As String
   On Error GoTo ParseName_Error

strUtil = Trim(strName)
strLastname = Left(strUtil, InStr(1, strUtil, ",") - 1)
strMiddle = Mid(strUtil, InStrRev(strUtil, " ") + 1)
    If Len(strMiddle) <> 1 Then
         strMiddle = vbNullString
    Else
        ParseName = strMiddle
        strUtil = Mid(strUtil, 1, Len(strUtil) - 2)
    End If
strFirstname = LTrim(Mid(strUtil, InStr(1, strUtil, ",") + 1))

Select Case strWhich
Case "F"
ParseName = strFirstname
Case "L"
ParseName = strLastname
Case "M"
ParseName = strMiddle
Case Else
ParseName = vbNullString
End Select


   On Error GoTo 0
   Exit Function

ParseName_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ParseName of Module Module4"
   
End Function

 

[vbaInet]

I have attached a small database with the funnctionality you want. Take a look at the sample and when you have questions, just let me know.

If you are not familiar with VBA code you many need a little assistance with that part.

This sample database file has one table, one form and VBA code behind the form that includes the user-defined function that actually does the spliting of the name.

Wow! You really did go the extra mile here. Test with this:

Doe Smith,John R

See what results you get for the forename and initials value.

To the OP (hhh1027): Maybe you should start considering having your fields stored as seperate entities after you've got this working. If, due to an input error, you had a name as this:

Doe,Smith,John

Then you will get incorrect results and this would then mean having to refine the code so that it splits from the last comma it encounters. Maybe your input screen doesn't allow comma as a valid character, I don't know. Just thought I point this out. If you want to get round this, disable the comma keystroke.

 

[Mr. B]

My code only accomidates the format that the OP posted as the existing format. If there are exceptions to the formatting then it would be expected that programming for those exceptionss would be required.

 

[hhh1027]

I have attached a small database with the funnctionality you want. Take a look at the sample and when you have questions, just let me know.

If you are not familiar with VBA code you many need a little assistance with that part.

This sample database file has one table, one form and VBA code behind the form that includes the user-defined function that actually does the spliting of the name.

Mr. B,

I have trouble downloading the attachment.

 

[hhh1027]

Here's a function that you could use

'----------------------------------------------------------------------------------
' Procedure : ParseName
' Author    : Jack
' Created   : 2/23/2010
' Purpose   : To parse a field containing the person's full name and to return
' the first name, or the initial if it exists, or last name depending on the
' value of strWhich.
'
' NOTE: The format of the fullname field is
'       Lastname, Firstname Initial(may not be present)
' eg    a)De Jesus, Charlene K.
'        b)O'Sullivan, Margaret
'----------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: strname == the person's fullname
'         strWhich = F  First Name
'                  = M  Middle Initial
'                  = L  Last Name
'
' Sample call:    ParseName(rs!FullName, "F")  ' to get First Name
'
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function ParseName(strName As String, strWhich As String) As String
  
Dim strUtil As String
Dim strLastname As String
Dim strFirstname As String
Dim strMiddle As String
   On Error GoTo ParseName_Error

strUtil = Trim(strName)
strLastname = Left(strUtil, InStr(1, strUtil, ",") - 1)
strMiddle = Mid(strUtil, InStrRev(strUtil, " ") + 1)
    If Len(strMiddle) <> 1 Then
         strMiddle = vbNullString
    Else
        ParseName = strMiddle
        strUtil = Mid(strUtil, 1, Len(strUtil) - 2)
    End If
strFirstname = LTrim(Mid(strUtil, InStr(1, strUtil, ",") + 1))

Select Case strWhich
Case "F"
ParseName = strFirstname
Case "L"
ParseName = strLastname
Case "M"
ParseName = strMiddle
Case Else
ParseName = vbNullString
End Select


   On Error GoTo 0
   Exit Function

ParseName_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ParseName of Module Module4"
   
End Function

I'm realy new to access just the basic query and forms. How do I use these codes and where do they go. Please help.

Thanks

 

[hhh1027]

I have attached a small database with the funnctionality you want. Take a look at the sample and when you have questions, just let me know.

If you are not familiar with VBA code you many need a little assistance with that part.

This sample database file has one table, one form and VBA code behind the form that includes the user-defined function that actually does the spliting of the name.

Mr. B thanks I got the sample and I will try. the problem was my browser google chrome would not let me download but I used IE and now i got it.

 

[hhh1027]

My code only accomidates the format that the OP posted as the existing format. If there are exceptions to the formatting then it would be expected that programming for those exceptionss would be required.

Mr. B Thanks the sample data. I have one question how do I use the codes to do update query so my table will be updated to split.

 

[Mr. B]

I have now added an Update type query to the sample database and have attached a new version of it.

Open the query in design view so you can see how I have created the functionality for each field that I want to update with a part of the original Name field. When run, the qurey will split the original Name field into the various parts and store each part in the appropriate field in the same table.

I have used parts of the original function in the new query to accomplish this result.

The original function and this new query will only work when the format for the original Name field is equal to the format you originally posted.

 

[hhh1027]

I have now added an Update type query to the sample database and have attached a new version of it.

Open the query in design view so you can see how I have created the functionality for each field that I want to update with a part of the original Name field. When run, the qurey will split the original Name field into the various parts and store each part in the appropriate field in the same table.

I have used parts of the original function in the new query to accomplish this result.

The original function and this new query will only work when the format for the original Name field is equal to the format you originally posted.

Mr.B
Thank you it worked perfect and I tested all the data and it is what I needed.

 

[Mr. B]

Gald to help.

Good luck with your project.