Excel has the default function Proper, which allows the ability to display only the first letter of a capitalized word; obviously, if that field contains more than one word, they are all written with the first letter capitalized, even if those words are separated by a hyphen (as is often the case with compound names or first names).
Access has a function that is somewhat similar: StrConv; I say somewhat “similar” because:
- has an extra argument – number 3 (using number 1 would convert everything to uppercase – the equivalent of UCase, and 2 would convert everything to lowercase – the equivalent of LCase);
- if we are talking about fields that contain compound words, with a hyphen, or that start with numbers, the second word will not be capitalized.
Public Function fc_PrimaLit(expr)
Select Case True
Case InStr([expr], "-") > 0
fc_PrimaLit = StrConv(Left([expr], InStr([expr], "-") - 1), 3) & "-" & _
StrConv(Right([expr], Len([expr]) - InStr([expr], "-")), 3)
fc_PrimaLit = StrConv(expr, 3)
A disadvantage would be if there were at least three words joined by a hyphen, that it would be only the first appearance written with the first capital letter.
A little deeper can be the following function exemplified on the Microsoft site:
' Scrie toate cuvintele avand prima litera majuscula.
' Poate fi utilizat in AfterUpdate of control; de ex., [Pren] = Proper([Pren]).
Dim txt_0, txt_1, txt_2, nr As Integer
If IsNull(expr) Then
txt_0 = CStr(LCase(expr))
txt_2 = " " ' Initializarea txt_2 la un singur spatiu, deoarece prima litera
' trebuie sa fie cu majuscula, dar sa nu aiba litera precedenta.
For nr = 1 To Len(txt_0)
txt_1 = Mid(txt_0, nr, 1)
If txt_1 >= "a" And txt_1 <= "z" And _
(txt_2 < "a" Or txt_2 > "z") Then
Mid(txt_0, nr, 1) = UCase(txt_1)
txt_2 = txt_1
Proper = txt_0