Proper Case and Exceptions

[kd2017]

Hi all!

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

It looks like it's only matching when the case of the word in the input string matches exactly the case in the table. I think the idea is that you can input whatever case and it will be 'autocorrected' to the case within the table.

I like the idea of being able to 'autocorrect' phrases and not just words. And I like that you can get rid of the regex. I wonder how long the exception list has to be before this route is less efficient than scanning the input string?

[accesstos]

It looks like it's only matching when the case of the word in the input string matches exactly the case in the table. I think the idea is that you can input whatever case and it will be 'autocorrected' to the case within the table.

By which way you think we can decide that the 'cat' in the original text is the CAT corporation and not the animal?
I think that the 'autocorrect' demands a lot of work to be 'intelligent' enough.

I like the idea of being able to 'autocorrect' phrases and not just words. And I like that you can get rid of the regex. I wonder how long the exception list has to be before this route is less efficient than scanning the input string?

I think that the methods of the DAO.Recordset and methods like InStr() are too efficient to worry about the size of the exceptions list. On the contrary, DlookUp() is not. Replace() function is the most 'heavy' method of ProperAll().

Cheers,
John