Hi all!
I think the opposite way is safer and more efficient.
That is, if the exceptions have removed initially from the input text, we can convert safely only the remaining words. The exceptions can be complex frases instead of single words and, by this way, we have only one query to the table of exceptions instead of multiple lookups.
Using the table of attached file of kd2017, I made the bellow procedure based on the above plan.
Code:
Function ProperAll(ByVal strIn As String, Optional ByVal SkipExceptions As Boolean = True) As String
'Returns the strIn with all words in proper case
'exept those that are in table tblCaseExceptions
'if SkipExceptions is TRUE.
Dim rsExp As dao.Recordset
Dim i As Integer
Dim intPos As Integer
Dim strExp As String
Dim strTemp As String
Dim strW As String
Dim varW As Variant
On Error GoTo ErrHandler
strTemp = strIn
If SkipExceptions Then
'Get the exceptions.
Set rsExp = CurrentDb.OpenRecordset("SELECT CaseException FROM tblCaseExceptions", dbOpenForwardOnly)
While Not rsExp.EOF
strExp = rsExp(0)
'Search for exception in temporary text.
If InStr(1, strTemp, strExp, vbBinaryCompare) > 0 Then
'Remove the exception from temp text.
strTemp = Replace(strTemp, strExp, " ")
End If
rsExp.MoveNext
Wend
End If
'Remove extra spaces.
While InStr(1, strTemp, " ") > 0
strTemp = Replace(strTemp, " ", " ")
Wend
'Get the remaining words.
varW = Split(Trim(strTemp))
'Convert the input text using only the remaining words.
For i = LBound(varW) To UBound(varW)
strW = varW(i)
'Find the current word in text.
intPos = InStr(intPos + 1, strIn, strW)
'Convert it to proper case.
Mid(strIn, intPos, Len(strW)) = StrConv(strW, vbProperCase)
'Set the position in text to the end of this word.
intPos = intPos + Len(strW)
Next i
ExitHere:
'Return.
ProperAll = strIn
On Error Resume Next
rsExp.Close
Set rsExp = Nothing
On Error GoTo 0
Exit Function
ErrHandler:
Resume ExitHere
End Function
@bcarter17
Adapt the names of the field and the table in OpenRecordset expression to the actual names of your objects, as kd2017 noted.
By setting the second optional argument to False, the procedure ignores the exceptions.
You can use the functions that provided when you need to display the text in proper case (in forms and reports) keeping the original text in table as it typed. Try a query like this:
Code:
SELECT ProperAll(nz([Your_Field])) AS Converted FROM [Your_Table];
Or, you can use it as ControlSource in a unbound textbox in a form/report like this:
Code:
=ProperAll(nz([Your_Field]))
Cheers,
John