INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
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 Modules (VBA Coding) FAQ
Functions
|
Proper Case function to handle surnames by BobStubbs
Posted: 13 Mar 05
|
I developed this function to correct text, such as customer names, typed into a text box on a form. Features:
-- Changes the initial letter of each word to a capital, e.g. john smith becomes John Smith
-- Optionally, changes upper case text to proper case, e.g. JOHN SMITH becomes John Smith
-- Handles 'special' surnames correctly, e.g. McDonald, O'Neill and Handley-Smith are capitalised correctly
To use this function, place it e.g. in the Lost_Focus event of a text box:
CODEtxtSurname=Propercase(txtSurname,0)
Here is the function:
CODEFunction ProperCase(strOneLine As String, intChangeType As Integer) As String
'---------------------------------------------------------------
'- This function will convert a string to Proper Case -
'- The initial letter of each word is capitalised. -
'- It will also handle special names such as O', Mc and -
'- hyphenated names -
'- if intChangeType = 1, all text is converted to proper case. -
'- e.g. 'FRED' is converted to 'Fred' -
'- if intChangeType = 0, upper case text is not converted. -
'- e.g. 'fred' becomes 'Fred', but 'FRED' remains unchanged. -
'---------------------------------------------------------------
Dim I As Integer
Dim bChangeFlag As Boolean
Dim strResult As String
'----------------------------------------------------------
'- No characters in string - nothing to do -
'----------------------------------------------------------
If Len(strOneLine) = 0 Then
ProperCase = ""
Exit Function
End If
'----------------------------------------------------------
'- Always set first letter to upper case -
'----------------------------------------------------------
strResult = UCase$(Left$(strOneLine, 1))
'----------------------------------------------------------
'- Now look at the rest of the string -
'----------------------------------------------------------
For I = 2 To Len(strOneLine)
'----------------------------------------------------------
'- If the previous letter triggered a capital, change -
'- this letter to upper case -
'----------------------------------------------------------
If bChangeFlag = True Then
strResult = strResult & UCase$(Mid$(strOneLine, I, 1))
bChangeFlag = False
'----------------------------------------------------------
'- In other cases change letter to lower case if required -
'----------------------------------------------------------
Else
If intChangeType = 1 Then
strResult = strResult & LCase$(Mid$(strOneLine, I, 1))
Else
strResult = strResult & Mid$(strOneLine, I, 1)
End If
End If
'----------------------------------------------------------
'- Set change flag if a space, apostrophe or hyphen found -
'----------------------------------------------------------
Select Case Mid$(strOneLine, I, 1)
Case " ", "'", "-"
bChangeFlag = True
Case Else
bChangeFlag = False
End Select
Next I
'----------------------------------------------------------
'- Special handling for Mc at start of a name -
'----------------------------------------------------------
If Left$(strResult, 2) = "Mc" Then
Mid$(strResult, 3, 1) = UCase$(Mid$(strResult, 3, 1))
End If
I = InStr(strResult, " Mc")
If I > 0 Then
Mid$(strResult, I + 3, 1) = UCase$(Mid$(strResult, I + 3, 1))
End If
ProperCase = strResult
End Function |
Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum
|
|
|
|
Talk To Other Members