PROPER Function of Excel in Microsoft Access ~ LEARN MS-ACCESS TIPS AND TRICKS

Thursday, November 5, 2015

PROPER Function of Excel in Microsoft Access

Introduction.

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

The Built-in Function PROPER() of Excel converts the first letter of each word in a string of text, into upper case and the rest of the text into Lower-case.

I don’t say there is no Function in Access to do that job, but its name is not PROPER()

Microsoft Access Function StrConv() can do what PROPER() Function does in Excel.

Its usage is as given below:

? StrConv("LeARn mS-access Tips aNd tRicKs",3)

Result: Learn Ms-access Tips And Tricks

The STRCONV() Function.

The STRCONV() Function needs two parameters:

The Text value to be converted
The conversion type parameter - is 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, and the 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 VBA 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().