name function

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

Kyler,

I see the problem. InStr returns a "-1" where there is no match.

To ease your problem, use the following piece of code:

sName = StrConv(InitialName, vbProperCase)

It will convert all the first letters of each word to uppercase. LCase the string first.

Allen Noakes
VB Programmer/Analyst
Dames & Moore Group