the following function returns a fully formatted name as long as a middle initial has been provided. It fails if only first name and last name have been entered. Any suggestions?

Public Function FormatTheName(InitialName As String) As String

Dim sName As String
Dim sFirstName As String
Dim sLastName As String
Dim sTemp As String
Dim idx As Integer
Dim sMi As String

'initialize the variable
sName = Trim(InitialName)

sFirstName = Left(sName, InStr(1, sName, " ") - 1)


sMi = Right(sName, Len(sName) - InStr(1, sName, " "))


sLastName = Trim(Right(sName, Len(sName) - InStr(1, sName, " ") - 1))

sTemp = Left(sLastName, 1)
sLastName = Right(sLastName, Len(sLastName) - 1)
sLastName = UCase(sTemp) & LCase(sLastName)

sTemp = Left(sFirstName, 1)
sFirstName = Right(sFirstName, Len(sFirstName) - 1)
sFirstName = UCase(sTemp) & LCase(sFirstName)

sTemp = Left(sMi, 1)
sMi = Right(sFirstName, Len(sFirstName) - 1)
sMi = UCase(sTemp) & "."

FormatTheName = sFirstName & " " & sMi & " " & sLastName

End Function