A Few Tips On Upper and Lower Case for Access

Author: Garry Robinson

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

This is nice because it only has to be done once in the form.

Jerry
Personable PC Solutions
(503) 281-2621
http://www.personablepc.com

 

Other Pages at VB123.com That You May Want To Visit

Duplicate Data Entry For Access
Microsoft FreeStuff
Take Advantage Of The Class Module Features Of Your Access Forms

Click on the button for the next help page in this Access Loop.