Split a full name into FirstName, Lastname in Access VBA

[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:

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



.Cell(1,1) in the first line is where the name goes in word, from
(Forms!frm_Main!CHName) in Access.

The format of the name in Access is

Smith, John A.

I would like it to show in Word in the format

John A Smith

I have been able to accomplish this using a specified amount of characters using Left and Right functions, but since the names will vary in length, specifying the amount of characters will not work.

Is there a way to get all text to the left of the comma and all text to the right?

Any help with this would be greatly appreciated

 

[Scott Huish]

Duplicate question. See http://idahobusinessreview.com/files/2015/06/Editorial-Cal_2015.pdf