Capitalising First Letters

Capitalising First Letters. Sometimes you may import data from another database, text files, etc. and this data may all be in upper case, i.e. “JULIE MISSON” and, for readability purposes, you wish to change it to proper case, i.e. “Julie Misson”.

When calculating first letters, one method to change the data from upper case to proper case is to use an update query with the StrConv function.

To create an update query:

Go to the Queries section of the database;

Click on ‘New’, then select ‘Design View’ from the list;

Add the table with the upper case field to the query and close the Add Table window;

In the Menu Bar, click on ‘Query’ then, choose ‘Update’ query from the drop down list.

You will notice an extra row added to your Query Design Grid with the label ‘Update To:’.

Add the field(s) which contains the data in upper case to the Query Design Grid; In the Update To row in the columns that contain the upper case text, add the following:

 StrConv([UpperCaseField],3)

Exchange [UpperCaseField] to the name of the field that contains the upper case data.

The ‘3’ signifies the vbProperCase constant. The other constants available are vbUpperCase (1) and vbLowerCase (2).

When finished, close and save the query. Double click on the query to run it. Be careful with update queries, as once the data is changed there is no going back.

You will receive two warnings - one to indicate you are about to run an update query, and then how many records will be updated.

Hope this is of some help.

Do you like this tip? Subscribe to my Newsletter to receive tips via email. Click Here to subscribe.