Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Other topics FAQ

Access Howto:

Capitalise Names including Macs and Mcs by AyJayEL
Posted: 18 Apr 01

This routine will capitalise names. Attach it to the AfterUpdate property of a name   e.g. FieldName = Surname     AfterUpdate: = Proper([Surname]).

Function Proper(pstrFld As Control) As Variant
' CONVERTS first letter of each word to upper case
' RETURNS converted text value
' NOTE converts most proper names correctly e.g. McKinvoy, O'Connor
' -----------------------------------------------------------------------------------------------
Dim intArraySize As Integer
Dim intArrayPos As Integer
Dim strReturnVal As String

If IsNull(pstrFld) Then
   Proper = Null
   Exit Function
End If

intArraySize = Len(Trim(pstrFld))    ' set size of array

pstrFld = LCase(pstrFld)       ' set all chrs to lowercase

ReDim strArray(intArraySize)   ' size array to hold field

For intArrayPos = 1 To intArraySize   ' fill the array with the field characters
   strArray(intArrayPos) = Mid$(pstrFld, intArrayPos, 1)
Next intArrayPos

strReturnVal = UCase(strArray(1))   ' upper case the first character

If strArray(1) = "M" Then  ' check if first name starts with Mac or Mc
   If intArraySize > 1 Then  ' make sure array is not 1 character only
      If strArray(2) = "a" And strArray(3) = "c" Then    
         strArray(4) = UCase(strArray(4))    ' upper case 1st letter after Mac
      End If
      If strArray(2) = "c" Then
         strArray(3) = UCase(strArray(3))    ' upper case 1st letter after Mc
      End If
   End If
   If strArray(1) = "O" Then   ' check if name starts with O'
      If strArray(2) = "'" Then
         strArray(3) = UCase(strArray(3))    ' uppercase character after O'
      End If
   End If
End If

For intArrayPos = 2 To intArraySize   ' go through the remaining letters
   '  check for separators  , - or .
   If strArray(intArrayPos) = " " Or strArray(intArrayPos) = "-" Or strArray(intArrayPos) = "." Then
      strArray(intArrayPos + 1) = UCase(strArray(intArrayPos + 1))    ' upper case the character after the separator
      ' check for Mac or Mc again
      If strArray(intArrayPos + 1) = "M" Then
         If strArray(intArrayPos + 2) = "a" And strArray(intArrayPos + 3) = "c" Then
            strArray(intArrayPos + 4) = UCase(strArray(intArrayPos + 4))  ' uppercase character after Mac
         End If
         If strArray(intArrayPos + 2) = "c" Then
            strArray(intArrayPos + 3) = UCase(strArray(intArrayPos + 3))  ' uppercase character after Mc
         End If
      End If
      If strArray(intArrayPos) = "O" Then  ' check if name starts with O'
         If strArray(intArrayPos + 1) = "'" Then
            strArray(intArrayPos + 2) = UCase(strArray(intArrayPos + 2))  ' uppercase character after O'
         End If
      End If
   End If
   ' add the next current character to the answer
   strReturnVal = strReturnVal + strArray(intArrayPos)

Next intArrayPos
' return the answer

pstrFld = strReturnVal
End Function

Take a little care where you use it or you may find it will save machine as MacHine! Other than that - enjoy!

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close