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.
A fast variation can be combinations of functions: Left, Right, InStr, either using SQL directly or integrated into a VBA function, so that it can be easily used whenever needed in that application.
1 2 3 4 5 6 7 8 9 |
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) Case Else fc_PrimaLit = StrConv(expr, 3) End Select End Function |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Function Proper(expr) ' 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 Exit Function Else 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) End If txt_2 = txt_1 Next nr Proper = txt_0 End If End Function |