|
|
Convert a string to ProperCase, lowercase, or UPPERCASE ... in expressions and VBA. Use StrConv, UCase, LCase, or a custom function, also called a user-defined function (UDF), to make more adjustments depending on what you need.
Data on the left came from another system. It is all UPPERCASE. Lower case letters are quicker to read and takes less space. So that each word is capitalized, we will convert to Proper Case.
On the right is a query. The first column is an expression using the GetProperCase custom function, which also makes other adjustments because the second (optional) parameter is True.
The 2nd and 3rd columns use the built-in StrConv function.
The reason that underscore (_) is added after the field name to make the calculated field name is to make it a different name without having to do a lot of thinking, and so the difference is not overly apparent.
On record 5, someone might go to the wrong address since the "E" on the end is cut off. On record 18, the city is chopped ... but a human would probably still know what it is.
The text in green boxes on the right is so you can compare the custom GetProperCase function with the built-in StrConv function to see some of the differences. Using StrConv, #C220 is changed to #c220 since "C" is actually the second character of the word, so StrConv misses it. Same with #A and #M7. The custom function also corrects Mcdonald to McDonald.
The built-in StrConv function is great! You can use it in expressions, and specify how you want the return string to be converted. In VBA, you can use the vbProperCase constant to specify the type of conversion; but expressions won't understand that, so in expressions, use 3 for the second parameter after the string to convert for Proper Case. You can also use StrConv for upper case and lower case, but the built-in UCase and LCase functions are easier to remember, and quick to use ... which is another reason GetProperCase defaults to no adjustments -- perhaps easier to remember than using StrConv with option 3.
If nothing is passed, GetProperCase returns a zero-length string, "", since it must return a string and nothing was explicitely assigned.
If what to do is on the same line as If then End If is not required (or allowed).
The error handler is set to skip any error so the user doesn't get error messages, if there are any. Then the default return value uses the StrConv function that is built-in to convert the case.
vbProperCase is a constant in VBA that evaluates to 3.
If booAdjustMore is not True, the function exits and all is done.
If adjustments are to be made, a string variable, sString is set to whatever StrConv already got. GetProperCase could be used instead of defining and using sString. It is my preference to manipulate variables instead of a function return value if there is very much to do.
A space character is temporarily added to the beginning of the string, to make it easier to find text to look for at the beginning of a word, and the first word too (if there is more than one). We don't want text to be made uppercase that is in the middle of a string (like changing "Tomcat" to "TomcAt").
The code looks for 3 cases (assigned to sLookFor in a loop) for words beginning with:
Add more cases as you look at your data and see they are needed.
If what to look for is found, and the string to convert has at least one more character after sLookFor, the character following what was found is made upper case using the built-in UCase function.
Note how the Mid function can be used on the left side of the equal sign to replace one or more characters (without having to do a combination of Left and Mid in an expression).
When done, the return value of GetProperCase is assigned to sString starting at the second character (to skip the temporary space that was added). Note that Mid does not need the 3rd argument, which is how many characters. If it is not specified, the rest of the string will be returned.
'*************** Code Start ***************************************************** ' Purpose :GetProperCase for a string, make optional adjustments ' Author : crystal (strive4peace) ' Return : String ' License : below code ' Code List: www.MsAccessGurus.com/code.htm '-------------------------------------------------------------------------------' GetProperCase
'------------------------------------------------------------------------------- ' Function GetProperCase(vString As Variant _ , Optional booAdjustMore As Boolean = False _ ) As String ' 181214 crystal (strive4peace) Dim iPos As Integer _ , iLen As Integer _ , i As Integer _ , sLookFor As String _ , sString As String On Error Resume Next If IsNull(vString) Then Exit Function GetProperCase = StrConv(vString, vbProperCase) 'vbProperCase=3 If booAdjustMore <> True Then Exit Function sString = GetProperCase sString = " " & sString 'temporarily add space to beginning For i = 1 To 3 'add cases as needed Select Case i Case 1 sLookFor = "#" Case 2 sLookFor = "Mc" Case 3 sLookFor = "O'" End Select 'add space to beginning so it is first part of word sLookFor = " " & sLookFor iLen = Len(sLookFor) iPos = InStr(sString, sLookFor) 'if found, make sure there is 'a character after and change to UPPERCASE If iPos > 0 And iPos + iLen <= Len(sString) Then Mid(sString, iPos + iLen, 1) _ = UCase(Mid(sString, iPos + iLen, 1)) End If Next i 'remove temporary space at beginning GetProperCase = Mid(sString, 2) End Function ' LICENSE ' You may freely use and share this code ' provided this license notice and comment lines are not changed; ' code may be modified provided you clearly note your changes. ' You may not sell this code alone, or as part of a collection, ' without my handwritten permission. ' All ownership rights reserved. Use at your own risk. ' ~ crystal (strive4peace) www.MsAccessGurus.com '*************** Code End *******************************************************
Share with others ...
here's the link to copy:
https://MsAccessGurus.com/VBA/Code/Fx_ProperCase_UPPERCASE.htm
or
http://MsAccessGurus.com/VBA/Code/Fx_ProperCase_UPPERCASE.htm