Split a Full Name into First name and Last name with VBA in Access

[adibakale]

As part of a application I am developing, I added a command button on a form that will send the customers Name and Address to a word template letter that gets mailed to them.

This is the code I am using for this part of the process:

Dim fullname As String, commaposition As Integer

fullname = "aabbccddeeffgg, aabbccddeeffgg"
fullname = Left(fullname, 14)

.ActiveDocument.Tables(2).Cell(1, 1).Select
.Selection.Text = Left(CStr(Forms!frm_Main!CHName), 4)


The format of the names look like:

LastName, FirstName Middle Initial.
Smith, John A

The code I pasted above (obviously) returns the first 4 characters.

Since the names will vary in the length of characters, I need to get all the text after the comma for the FirstName and middle initial, and all the text before the comma for the LastName.

I declared commaposition As Integer, but I haven't figured out if that is the correct way to do this or how to use it.

I am pretty new to VBA and I am having a little difficulty with this, any help would be greatly appreciated. Thank you

 

[Micron]

this has no error checking and is untested. I do not know if you can always rely on firstname,comma space first space lastname

function getName ()as string

dim strName as string, strFirstPart as String, strLastPart as String
dim intPos as integer

intPos = Instr(strName, ",")' get the position of the comma
strLastPart = Mid(strName,intPos+2) 'add 2 to ignore the comma and space
strFirstPart = Left(strName,intPos-1) 'minus 1 to get everything to the left of the comma

getName = strFirstPart & " " & strLastPart

end function