How to Convert Text or String to Lowercase, Uppercase, and Propercase




How to Convert Text to Lower Case, Upper Case, and Proper Case

Many people think formatting text is a small part of programming. However, it appears to be an important part of the program and database. When the formatting function is integrated to the entry form, it will provide a convenience to the users. It will auto convert to correct the data format event though the user enters wrong format. It will keep consistency of data in the database. Also the input data will be recorded in the same format and will be useful for a later use.

In this How To, I will show How to Convert Text to Lower Case, Upper Case, and Proper Case by using VB code, Macro, and Property Sheet format.

Convert Text/String to Upper Case

Sometime we will need a certain field to have the text in a capital format as uppercase. For example, ProductCode field contains BEW1000 or State field contains CA, CO, NV, TX, etc. Users may enter data in different ways. Some may be not compatible with using the Caps Lock or Capital letter. It will be sufficient way that the users can enter any type as they want, but program will convert it to capital letter or upper case.

There are many different ways can be done to convert text to the uppercase as shown below. For example, we can convert the CustomerName text field to Upper Case by using UCase() or StrConv() function as shown below.

Me.CustomerName= UCase(Me.CustomerName)

Me.CustomerName= StrConv(Me.CustomerName, vbUpperCase)

Me.CustomerName= StrConv(Me.CustomerName, 1)

Note: A number 1 from StrConv(Me.CustomerName, 1) is representing the value of conversion argument settings. More information about the value of strConv() function can be found here.

Use Code:

Use VB code under the AfterUpdate() event of the CustomerName field.

Private Sub CustomerName_AfterUpdate()

Me.CustomerName = UCase(Me.CustomerName)

End Sub

afterupdate-event

Use Macro:

macro-to-upper-case

Example after conversion to Uppercase:

Nancy Lopez => NANCY LOPEZ

nancy lopez => NANCY LOPEZ

NANCY m lopez => NANCY M LOPEZ

 

Use Property Sheet Format with a Greater than sign (>)

We can use a format under the Property Sheet of a field to display the capital letter or uppercase instead of using VBA code. We use the Greater than sing (>) to display text as Uppercase as shown in the picture below. I don’t the word “Convert” for this format because it just display on the form that you use this format. The original entered data/text will be saved in the database table, not the Uppercase. In another word, this property sheet format will not affect the input data.

Example of using Greater than sign form (>)

Nancy Lopez => NANCY LOPEZ                 saved as Nancy Lopez

NANCY m lopez => NANCY M LOPEZ       saved as NANCY m lopez

property-format-to-upper-case

 

Convert Text/String to Lower Case

Sometime we will need a certain field to have the entry data in a lowercase format. This method is not quite popular use.

There are many different ways can be done to convert text to the lowercase as shown below. For example, we can convert the CustomerName text field to Lowercase by using LCase() or StrConv() function as shown below:

Me.CustomerName= LCase(Me.CustomerName)

Me.CustomerName= StrConv(Me.CustomerName, vbLowerCase)

Me.CustomerName= StrConv(Me.CustomerName, 2)

Use Code:

Private Sub CustomerName_AfterUpdate()

Me.CustomerName = LCase(Me.CustomerName)

End Sub

Use Macro:

macro-lcase

Example after conversion to Lowercase:

Nancy Lopez => nancy lopez

nancy m lopez => nancy m lopez

NANCY lopez => nancy lopez

 

Use Property Sheet Format with a Less than sign (<)

We can use a format under the Property Sheet of a field to display the small letter or lowercase. We use the Less than sing (<) to display text as lowercase as shown in the picture below. The original entered data/text will be saved in the database table, not the lower case. In another word, this property sheet format will not affect the input data.

Example of using Less than sign form (<)

Nancy Lopez > nancy lopez          saved as Nancy Lopez

nancy m lopez > nancy m lopez saved as nancy m lopez

property-format-to-lower-case

The difference between Text Format using Property Sheet format (<,>) and VBA code under the AfterUpdate Event

Format on Property Sheet Format under AfterUpdate Event
Text/String Entry Nancy Brown Nancy Brown
Format type < LCase(Me.CustomerName)
Text displays on form nancy brown nancy brown
Text is saved in table Nancy Brown (No Change) nancy brown
Format type > UCase(Me.CustomerName)
Text displays on form NANCY BROWN NANCY BROWN
Text is saved in table Nancy Brown (No Change) NANCY BROWN




Convert Text/String to Proper Case (Title Format)

Sometime we will need a certain field to have the entry data in a Proper Case format. This method is most used in many fields such name, address, etc.

There are few ways to convert text/string to the proper case as shown below. For example, we can convert the CustomerName text field to Lower Case by using StrConv() function as shown below:

Me.CustomerName= StrConv(Me.CustomerName, vbProperCase)

Me.CustomerName= StrConv(Me.CustomerName, 3)

Me.CustomerName= StrConv([CustomerName], 3)

Use Code:

Private Sub CustomerName_AfterUpdate()

Me.CustomerName = StrConv(Me.CustomerName, vbProperCase)

End Sub

Use Macro:

macro-proper-case

Example after conversion to Proper Case:

Nancy lopez => Nancy Lopez

nancy lopez => Nancy Lopez

NANCY m lopez => Nancy M Lopez