First Letter Uppercase Only Access Tables

This article explains how you to create a macro that will force the first letter of a field to appear as uppercase and the rest as lowercase no matter what the user inputs.

Consider the table below:
Sample Table
We want to assign a macro to this table that will convert the first letter of the fields [First Name] and [Last Name]  to uppercase, while converting the rest to lowercase.


Contents

Step 1:

Select and open the table you want to apply the macro to from the Navigation Pane on the left. Click on the Before Change button on the Table ribbon:
Before Change


Step 2:

Select SetField from the drop down list:
SetField


Step 3:

After selecting SetFields, 2 fields will be added:
2 Fields

Name: This is the name of the field that will be modified. In this case it is [First Name]
Value: This is the value that the field will be change to. In this case use the expression below:

UCase(Left([First Name],1)) & LCase(Right([First Name],Len([First Name])-1))

Expressiokn


Step 4:

Repeat step 3 for the [Last Name] field. Use the expression below:

UCase(Left([Last Name],1)) & LCase(Right([Last Name],Len([Last Name])-1))

Last Name


Step 5:

Save and Close the Macro:
Save and Close


Result:

No matter how the user inputs the first and last names, the first letter will be converted to uppercase and the rest of the characters will be converted to lowercase.

Before:
User Input
After:
Result

You can download the sample file used in this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *