Search The Web

Today's Headlines

Friday, January 7, 2011

Microsoft Access Tips & Tricks: Propercasing Text Entries

In many instances, it makes sense to store the user's entries in the database after formatting it correctly. In particular for text entries, this may include converting the entry into the proper case so that it is capitalized correctly. This post talks about some common ways to handle this beautification task.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields,finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, highlighting form controls, conditional formatting, performing date manipulations, counting letters, words, sentences and paragraphs, and calculating age.

There are a couple of things that I as a database designer find useful to do for every text entry in my databases. I am not claiming that every database designer should therefore always do these things. I do them because I find the results pleasing, and it also helps to solve some problems that people face when searching for or filtering for results in a database.

The first of these is purely intended to enhance the visual aspect of text entries in a form. In general, you want to make sure the text is formatted with the correct capitalization before being stored in the table. I dislike storing text the way users enter it because different users get into the habit of entering text in different ways, and the table entries become a mishmash of different styles of text entry. This not only affects the way the results look, but may also result in difficulties when it comes to searching for and locating specific entries if what you are searching for was not entered with the same mix of lower case and upper case letters.

What I do is to convert all text entries to the most logical format in the afterupdate() event of the appropriate form control. In most cases, the proper and most logical format is one in which the first letter of each word is capitalized (this will work for most things like names and addresses). Sometimes, the logical format is one in which the first letter of the entry is capitalized and all other letters are in lower case. Either way, the aim is to achieve consistency.

The main function to use for this purpose is StrConv(). This is a powerful function with several options that make it easy to format text the way you want. As you can see from the Microsoft help page, there are a large number of possibilities for the conversion argument that make the function very versatile. If you want to convert the text entry in a form control to have upper case letters at the beginning of each word, you can use it as below:
Private Sub MyTextControlAfterUpdate()
Me.MyTextControl = StrConv(Me.MyTextControl, vbProperCase);
End Sub
If, on the other hand, you just want to convert the entire entry to lower case, you can use the function below:
Private Sub MyTextControlAfterUpdate()
Me.MyTextControl = StrConv(Me.MyTextControl, vbLowerCase);
End Sub
And obviously, you can use the function below to convert everything to upper case:
Private Sub MyTextControlAfterUpdate()
Me.MyTextControl = StrConv(Me.MyTextControl, vbUpperCase);
End Sub
In almost all cases, the results when you use vbUpperCase and vbLowerCase are exactly as you expect them. However, the results from vbProperCase can sometimes be unexpected. In particular, the software has no intelligent way of dealing with names like McDonald, O'Donnell, etc. When you pass the string "ronald mcdonald" through StrConv() with the second argument being vbProperCase, the output is "Ronald Mcdonald", not what you want, which in most cases is "Ronald McDonald".

To deal with anomalous capitalizations like the one above, I always provide the user an out so that the automatic formatting of text does not keep converting the user's entries into something the user does not want. To provide this out, I compare the first letter of the entry with its lower case equivalent. If the user started the entry with a lower case letter, then my function will try to propercase it. If the user began with a capital letter, I leave the entry alone. I train my users to enter names like Ronald McDonald with an initial upper case letter (and enter it exactly as they want to see it), while most of the time, they can enter names like Robert Jones in all lower case letters (which is much simpler since they don't have to keep hitting the shift key) and have the software automatically propercase it. Our afterupdate() function then takes on the form below:
Private Sub MyTextControlAfterUpdate()
if Asc(Lcase(Left(Me.MyTextControl, 1))) = Asc(Left(Me.MyTextControl, 1)) then
Me.MyTextControl = StrConv(Me.MyTextControl, vbProperCase);
end if
End Sub
If you want, you can change the code above to check for an initial capital letter, and then propercase only entries that don't have that.

But this can still result in problems. In particular, addresses can cause problems because they can contain words like McDonald, but the user can not start the address with a capital letter because most addresses start with a number. So, the user may be frustrated with his inability to control the software that happily converts entries like "11 McDonald Drive" to "11 Mcdonald Drive".

How do you prevent this? The trick I have used for this kind of problem is to train the user to start such entries with a space. I then use the following code to check for either the presence of the space or the presence of an uppercase letter, and then propercase the entry. At the end, I also trim the entry so that the space the user entered is eliminated from the final entry. The code looks as below:
Private Sub MyTextControlAfterUpdate()
if (Asc(Lcase(Left(Me.MyTextControl, 1))) = Asc(Left(Me.MyTextControl, 1))) _
or (Left(Me.MyTextControl, 1) = " ") then
Me.MyTextControl = StrConv(Me.MyTextControl, vbProperCase);
Me.MyTextControl = Trim(Me.MyTextControl);
end if
End Sub
This gives the user full control over their text entries while still harnessing the power of the computer to beautify their entries where and when they leave it up to the computer. The most simple way to enter text automatically engages the computer in beautifying and cleaning it up, while the user just has to enter strings with hard to capitalize entries the way they want to see it (and add a space in front of the entry if necessary to trick the computer into leaving the entry alone).

This makes sure all the text entries in the database are consistent, making it easy to put the entries in reports and other presentation outputs without having to do a lot of further formatting.

I also use the StrConv() function extensively for searches. Most of the searches in my databases are based on text entries entered into a form. Before I take the user entry and start comparing it against the entries in the tables, I use StrConv() and Trim() in combination to make the text all lower case with no spaces at either end. I then use the same combination to make the table entries all lower case with no spaces at either end. These strings are then easy to compare against each other and always yield consistent results without any false negatives caused by inconsistent capitalization, padding with spaces at the beginning or end, etc.

Hope this post has been helpful in solving any problems you might have had with formatting text (propercasing text entries in particular) in Access. The VBA code in this post has been tested in Access 2003 and should work without any problems in all versions of Access from Access 97 on up. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!

No comments:

Visitors Country Map

Free counters!

Content From

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!



Spelling Bee
difficulty level:
score: -
please wait...
spell the word:

Search The Web