VBA Change Case


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.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))

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
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

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.

for i=1 to len(strChangeCaseCty)
if i=1 then
strChangeCaseCty=ucase(mid(strChangeCaseCty,i,1)) & mid(strChangeCaseCty,i+1,len(strChangeCaseCty))
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))
end if
end if

(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.


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

Open Email Using SendObject Access Download User Inactivity Logout VBA Code Disable Shift Key Demo Send Email Microsoft Access Tutorial Download (Advanced) Sequential Counter in Query Multi Select List Box Query Parameters Row Level Data Security Programming MS AccessSecurity Alternative Single User Inventory Calculations Download How To PerformMulti-User Inventory Calculations Make Dependent Combo Box Code MS Access Tutorial Union Query Example (Simple) Union Query (Advanced) Access Tutorial How ToFill Fields From Combo Box Use Global Variables as Parameters Continuous Form Dependent Combo Box How To Program Continuous-Continuous Master/Detail Forms Access Bar Chart / Bar Graph Programming Crosstab Query Example TransferText & OutputTo Microsoft Access DoCmd.OpenForm & OpenArgs VBA Example Running Sum Query Method Choose Command Dynamic SQL Order By Access Conditional Format Access Report Banding

Popup Form Control Method

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016