Changing the Case of Text

Changing the Case of Text

If you wish to ensure that the data is entered in a set style in the database, then you can use various text formatting functions to achieve this.

To ensure that a field is always entered as upper case, you can use either StrConv, or else UCase:

Private Sub txtName_AfterUpdate
Me!txtName=UCase(Me!txtName)
End Sub

Private Sub txtName_AfterUpdate
Me!txtName=StrConv(Me!txtName,vbUpperCase)
End Sub

To change the text to lower case, you can again use StrConv, this time with an argument of vbLowerCase instead of vbUpperCase, or else use LCase.

To change the text to proper case, you will either need to use StrConv, or else, if you want greater flexibility and accuracy, to use a custom function (StrConv will convert 'MacDonald' to 'Macdonald'). An example of a custom function can be found at The Access Web.

Private Sub txtName_AfterUpdate
Me!txtName=StrConv(Me!txtName,vbProperCase)
End Sub

If you wish to ensure the first letter is a capital, and then the rest of the field is as entered by the user, you will also need to use Left and Mid functions:

Private Sub txtName_AfterUpdate
Me!txtName=UCase(Left(Me!txtName,1)) & Mid(Me!txtName,2)
End Sub

A final note about StrConv is that although you are able to use the VBA constants, such as vbProperCase, from within VBA, if you use a query, for example to update previously entered data, then you will need to use the numerical equivalent instead (vbProperCase=3).

Top

Last modified at 06/06/2006 14:54:53