Proper Case question

[ShannonMarie]

I am searching for syntax to implement the Proper function for names and words that have more than one capital letter. For instance McDonald, or Some City. I have looked in the help files and it only offers a basic syntax. If anyone can help it would be greatly appreciated. Thanks.

 

[Fornatian]

try this... will continue on next reply

Function ProperLookup(ByVal InText As Variant) As Variant
'
' Similar to Proper(), but uses a table (NAMES) to look up words that don't fit the
' general formula.
'
Dim OutText As String, Word As String, i As Integer, C As String
Dim Db As DATABASE, t As Recordset
'
' Output Null and other non-text as is
'
If VarType(InText) <> 8 Then
ProperLookup = InText
Else
Set Db = CurrentDb
Set t = Db.OpenRecordset("Names", dbOpenTable)
t.Index = "PrimaryKey"
OutText = ""
Word = ""
For i = 1 To Len(InText)
C = Mid$(InText, i, 1)
Select Case C
Case "A" To "Z" ' if text, then build word
Word = Word & C
Case Else
If Word <> "" Then ' if not, then append existing word and then the character
t.Seek "=", Word
If t.NoMatch Then
Word = UCase(left(Word, 1)) & LCase(Mid(Word, 2))
Else
Word = t!Name
End If
OutText = OutText & Word
Word = ""
End If
OutText = OutText & C
End Select
Next i
'
' Process final word
'
If Word <> "" Then
t.Seek "=", Word
If t.NoMatch Then
Word = UCase(left(Word, 1)) & LCase(Mid(Word, 2))
Else
Word = t!Name
End If
OutText = OutText & Word
End If
'
' Close table and return result
'
t.Close
Db.Close
ProperLookup = OutText
End If
End Function

 

[Fornatian]

the Names table currently has these entries:

NewName
BA
BS
DC
de
der
DO
II
III
IV
IX
le
MacDonald
McCloud
McDonald
McEwan
McLeod
McQueen
MD
PhD
van
VI
VII
VIII
XI
XII

 

[ShannonMarie]

WOW

Thanks Fornation! I thought there might be a simple insert that I just had not seen yet! This is soo helpful. A lot of work to build a "Names" table at a college, but a persons gotta do whatever works, right? I know that the reply took effort and time and I really appreciate it. My partner and I will start building.

 

[Mile-O]

Just as an extra, here's one I wrote for my database: It's different and doesn't bother with Roman Numerals as I never see names popping up with them.

Function CorrectNames(ByVal strName As String) As String

    On Error GoTo Err_CorrectNames
    
    Dim intCounter As Integer
    
    strName = StrConv(strName, vbProperCase)
    
    If InStr(1, strName, "Mc") Then _
        strName = Left(strName, InStr(1, strName, "Mc") - 1) & "Mc" & _
        StrConv(Mid(strName, InStr(1, strName, "Mc") + 2), vbProperCase)
        
    If InStr(1, strName, "Mac") Then _
        strName = Left(strName, InStr(1, strName, "Mac") - 1) & "Mac" & _
        StrConv(Mid(strName, InStr(1, strName, "Mac") + 3), vbProperCase)
        
    If InStr(1, strName, "'") Then
        If InStr(1, strName, "O'") Then
            strName = Left(strName, InStr(1, strName, "O'") - 1) & "O'" & _
                StrConv(Mid(strName, InStr(1, strName, "O'") + 2), vbProperCase)
        Else
            strName = Left(strName, InStr(1, strName, "'") - 2) & LCase(Mid(strName, InStr(1, strName, "'") - 1, 1)) & "`" & _
                StrConv(Mid(strName, InStr(1, strName, "'") + 1), vbProperCase)
        End If
    End If
        
    CorrectNames = strName
    
Exit_CorrectNames:
    Exit Function
    
Err_CorrectNames:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CorrectNames

End Function

 

[ShannonMarie]

Thanks In Chernobyl Pond

I like the idea of checking for certain values too! The project is for a college. I gave the information to my partner (who is also one of the main users) to let her decide. I will submit this as well. I appreciate you sharing with me>