VBA
Case
Change: Uppercase & Lowercase Text
Changing the case of text in a Microsoft Access database is
often required when importing data from an external source.
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 convert all upper case names to mixed
case, or proper case in most cases.
' vb 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 an 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 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
(only sure this works 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 runable solutions to common programming issues.