Garry Robinson's Popular MS Access, Office and VB Resource Site


Home  Contact Us

Order Software

Search vb123

 Smart Access  
The Magazine that Access Developers loved to read and write for is back
Article Index Here or
Purchase Here

RSS & Newsletter  
Join our XML/RSS Newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
Sign up here

Get Good Help
If you need help with a database, our Australian Professionals could be the answer
Read More

123 For Office

Great shortcuts, supports Office 2013 to 2003, easy zipping.
Read More

  The Workbench  Find out who has your database open, start the correct version of Access, easy compacting and zip backups, change startup options,  compile, shutdown database
Read and Download

Access > SqlServer 
Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query
translation and web form conversion.
Read More

Like FMS Products?
Purchase them from us and get a free Workbench or Smart Access  More

Garry's Blog
Find out a few other things that Garry has been writing about Microsoft Access. Read more

About The Editor Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia
Contact Us ...



Next Tip   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.

Personable PC Solutions
(503) 281-2621


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 Next Tip button for the next help page in this Access Loop.


Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals