PROPER Function of Excel in Microsoft Access

PROPER Function in Excel and Microsoft Access.

The UCase() Function in Access converts all letters in a string to uppercase letters — the UPPER() Function in Excel.

? UCase("LeARn mS access Tips aNd tRicKs")

Result: LEARN MS ACCESS TIPS AND TRICKS

LCase() Function - in Excel LCase("LeARn ms access Tips and tricks")

? LCase("LeARn mS-access Tips aNd tRicKs")

Result: learn ms-access tips and tricks

The Built-in Excel Function PROPER() formats the first letter of each Word in Capital letters and the rest of the word in lowercase letters. 

There is a Function in MS Access to do that job, but its name is not PROPER.

Microsoft Access Function StrConv() can do what the 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:

1. The Text value to be converted
2. The conversion type parameter is an integer value.

   Here, parameter value 3 is for Proper-case conversion.

I know what you will ask: can we do other conversions with this Function?

Yes, STRCONV(Text,1) for UCase() Function and STRCONV(Text,2) for LCase() Function

The STRCONV() function supports other uses through different parameter values. To explore these further, type STRCONV in the Microsoft Access Help documentation Search Box to find detailed information.

If you find the uppercase function StrConv() complicated, we can define an Access equivalent of Excel’s PROPER() function.

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, copy and paste the code into it and save.

I will not be surprised if you name the function as PCase(), following the same pattern as other text conversion functions LCase() and UCase().

1. Roundup Function of Excel in MS-Access
2. Proper Function of Excel in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007 
7. Microsoft Excel Power in MS-Access
8. Rounding Function MROUND in Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel-2
11. Opening Excel Database Directly
12. Create Excel Word File from Access

Read More