A Few Tips On Upper and Lower Case for Access

A Few Tips On Upper and Lower Case for Access

This was the code that I concocted for the incorrect case phenomenon. Just thought I would share it with the readers as it seemed to work well. If you have a better way, let us know. MyFieldName is the name of the Table field or Form field that we want to change case on. You can also use the LCase function to change text from upper to lower case

First sample updates the MyFieldName name if it contains any lower characters just before the user saves the record. It does this using the BeforeUpdate event of an Access form.


Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error Resume Next

If StrComp(UCase(MyFieldName.Value), _
MyFieldName.Value, vbBinaryCompare) <> 0 Then

MyFieldName .Value = UCase(MyFieldName .Value)
End If

End Sub

Here is a query to find the lower case names in the tblName table

SELECT MyFieldName FROM tblName
WHERE (((StrComp(UCase([MyFieldName ]),[MyFieldName ],0))=-1));


A query to update any lower case names in the tblName table.

UPDATE tblName SET MyFieldName = UCase([MyFieldName ])
WHERE (((StrComp(UCase([MyFieldName ]),[MyFieldName ],0))=-1));

Input From Jerry

Why bother checking for lower case? Why not just do it:

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error Resume Next
MyFieldName.Value = UCase(MyFieldName.Value)
End Sub

Seems like simpler code and probably less processing overall. Too bad there isn't an Input Mask code to convert to upper case without specifying the number of characters.

Also I use the following in one form to make everything upper case as it's typed in:

Private Sub Form_KeyPress(KeyAscii As Integer)
'Convert to upper case
KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub