Want to >L< a field in VBA but it wont let me 1

[Scoty]

Hey Guys,
I have a database which pulls information from a legacy system which puts all information in CAPS I want to go through and make the information look right. Such as "Firstname Lastname", right now the inforation is "FIRSTNAME LASTNAME" and this just doesn't look right on letterhead. Can someone please help.
Thanks
Scoty
:

 

[scking]

Make sure the input string for each name is trimmed on both sides and does not contain any double spaces. The solution to this one in contained the 'General Discussion' forum, just sort by stars.

Public Funtion GetFullName(strFName As String, strLName As String) As String

Dim strFullName As String
strFullName = UCase(Mid$(strFName, 1, 1) _
& LCase(Mid$(strFName, 2) _
& " " & UCase(Mid$(strLName, 1, 1) _
& LCase(Mid$(strLName, 2)
GetFullName = strFullName
End Function

I am aware that it could be simplified by not using the variable strFullName but prefer to use one for debugging.

Steve King

 

[MichaelRed]

The following function will convert any string into "Proper" case.

Public Function basProperName(strNameIn As String) As String

    Dim strProperName As String
    Dim Idx As Integer

    strProperName = Format(Trim(strNameIn), &quot;<&quot;)    'All lower case
    Idx = 1                                         'Initalize Index

    'First Letter
    Mid(strProperName, Idx, 1) = UCase(Mid(strProperName, Idx, 1))

    While Idx <> 0
    
        Idx = InStr(Idx + 1, strProperName, &quot; &quot;)
        Mid(strProperName, Idx + 1, 1) = UCase(Mid(strProperName, Idx + 1, 1))

    Wend

    basProperName = Trim(strProperName)

End Function

You could use it in a query as:

ProperName: basProperName(["FieldNameForName'])

Or

Directly in the report:

ControlSource:
=basProperName(["FieldNameForName'])

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Scoty]

I'm sorry,
Do I place this as a module? do I place it in the code? let me know
Scoty
:

 

[MichaelRed]

Either function needs to be placed in a module. Mine has examples of how to instantiate the function to get the return value, either from a query or from directly in the report.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over