PROPER Function of Excel in Microsoft Access
We have the UCase() Function in Access to convert all letters in a string of Text into upper-case letters(UPPER() Function in Excel).
? UCase("LeARn mS-access Tips aNd tRicKs") Result: LEARN MS-ACCESS TIPS AND TRICKS
LCase() Function (LOWER() in Excel) of Access converts all the Text in a string into Lower-Case letters.
? LCase("LeARn mS-access Tips aNd tRicKs") Result: learn ms-access tips and tricks
PROPER() Built-in Function of Excel converts first letter of each word in a string of text into upper-case and rest of the text into Lower-case.
I don’t say there is no Function in Access to do that job, but it’s name is not PROPER()
Microsoft Access Function StrConv() can do what PROPER() Function does in Excel.
It’s usage is as given below:
? StrConv("LeARn mS-access Tips aNd tRicKs",3) Result: Learn Ms-access Tips And Tricks
STRCONV() Function needs two parameters:
- The Text value to be converted
- The conversion type parameter – an integer value.
Here, parameter value 3 stands for Proper-case conversion.
I know what you are thinking; can we do other conversions also with this Function?
Yes, STRCONV(Text,1) for UCase() Function
and STRCONV(Text,2) for LCase() Function.
There are other usages for the STRCONV() function, with different parameters. If you are interested to explore further then type STRCONV in the search control of Microsoft Access Help Documents and get the details.
If you think that the name of the function is difficult to memorize, requirement of a second parameter is also too much work then we can go by the Excel way and define a PROPER() Function in Access and use it wherever you want.
Here is the Code:
Public Function PROPER(ByVal strText As String) As String PROPER = StrConv(strText, 3) End Function
Copy and Paste the above VBA Code into a Global VBA Module of your Project. If you have a Library Database then copy and paste the Code into it, so that you don’t have to duplicate it in all your other Projects.
I will not be surprised if you name the function as PCase() in line with the other text conversion functions LCase() and UCase().