Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

Upper/Lower Case VB
VBA Proper Case






Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail #1
Send Gmail Email #2
Inactivity Logout Code
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Concatenate Records
Stock Quotes
CreateQueryDef
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
FindFirst
Access Version
Global Variable Parameter
Global Variables
Active Labels
Files List Box
Mail Merge
Quick Sort
Recordset Filters
Reference Form Field
Select Case
Access Transactions


Visual Basic Function Examples


Case Change in Access

Uppercase & Lowercase Text

Changing the case of text in a Microsoft Access database is often required when importing data from an external source. Change case is great skill to have when once in a while you'll come across a set of data that contains all upper case letters.



Blue Claw Database Design is your premier source of Microsoft Access programming and database support. Both small business and department-level databases. Desktop installation as well as cloud/internet based solutions.

With our new partnership with IT Impact we have extensive programming support in SQL/Server database, MS Access software, Power BI applications, .Net web development, PHP, Excel & Outlook customization.


When these recordsets contain names and addresses the all capital words make for a too informal address label.  Below is a simple visual basic subroutine that will switch all upper case names to mixed case, or proper case in most cases.  Learning how to do string manipulation for data parsing is an import tool to have in your knowledge base.

'  vba change case subroutine:
Public Sub change_case(iline As String)
Dim ilen As Integer
Dim i As Integer
Dim ic As Integer
Dim ispace As Boolean
Dim new_line As String
Dim Iasc(100)
Dim Inew(100)
ilen = Len(iline)
ispace = False
'
' load each letter into a VBA array
'

For i = 1 To ilen
    Iasc(i) = Asc(Mid(iline, i, 1))
Next

new_line = ""
ispace = True
'
' parse each letter and based on previous letter or space convert text to
' lower case
'

For i = 1 To ilen
    Select Case Iasc(i)
        Case 65 To 90                        ' uppercase letters
            If ispace = False Then
                Iasc(i) = Iasc(i) + 32        ' change to lowercase
            Else
                ispace = False
            End If
        Case 32                                 ' space
            ispace = True
        Case 48 To 57                        ' numbers
            ispace = False
        Case Else
            ispace = True
    End Select
    new_line = new_line & Chr(Iasc(i))    ' build the new line
Next

iline = new_line
End Sub

The change case example's basic concept is to leave all letters following a space as uppercase and change those following something else to lowercase.  Actually, the visual basic coding may be simpler by changing to upper case only letters following a space and the rest to lower case - give it a try.

ispace=0
for i=1 to len(strChangeCaseCty)
    if i=1 then
        strChangeCaseCty=ucase(mid(strChangeCaseCty,i,1)) & mid(strChangeCaseCty,i+1,len(strChangeCaseCty))
    else
        if mid(strChangeCaseCty,i,1)=" " then ispace=1
        if ispace=1 and mid(strChangeCaseCty,i,1)<>" " then
      strChangeCaseCty=mid(strChangeCaseCty,1,i-1) & ucase(mid(strChangeCaseCty,i,1)) & mid(strChangeCaseCty,i+1,len(strChangeCaseCty))
    ispace=0
    end if
end if
next


(this may only work with letters)

From this visual basic routine you can expand it to include specific VB code for special situations such as Mc, Mac, etc.

Don't miss our VB Code Downloads for runtime solutions to common programming issues.










Blue Claw Database Design Downloadable Tutorial:
How To Perform Inventory Calculations (Multi-User)  








A Blue Claw Software Design Template:

Investment Account Management Software
 










A Blue Claw Database Design Article:

Repair Corrupted Access Table
 

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & Sharepoint


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, 2010, 2013 & 2016