Change to proper case...with exceptions

[PasJes]

I know there are ways of easily changing case in Access...to upper, lower or proper.

Just wondeirng if there is any code anyone has that builds in exceptions when trying to convert to proper case.

For example, words (or name) like O'BRIEN...should convert to O'Brien....not O'brien.

And to ignore hyphens, so that MERCEDES-BENZ gets converted to Mercedes-Benz and not Mercedes-Benz.

I'm fairly new to all this...so any assistance greatly appreciated.

[CJ_London]

Often the requirements are quite specific so need to be handled on a trial and error basis so take a look at the similar threads section at the bottom of this thread

to handle ‘ and - you can use the split function on the appropriate character then rebuild the string - but then you might find even that is not enough. For example ‘de la Rue’ only wants the last element with a capital letter

[ArviLaanemets]

Unless the number you have to deal with such exceptions is very big, maybe you can simply create a table of exceptions - with a field for value to convert, and one for converted value (or several ones for different conversion rules). When converting table field, you join this exceptions table into your replace query. When join field is not null, you read converted value from conversion table, otherwise you apply Upper(), Lower(), or Proper().

'Mercedes-Benz and not Mercedes-Benz' - what is the difference?

[Welshgasman]

Here is something I used in one of my Dbs.
You will likely need to modify it for any other types?, but at least it is a start.
You will need to remove the .txt extension, as the site does not accept .bas files.

[CJ_London]

just put this together - Edit - missed a bit

Function properCase(str As String) As String
'could put these in a table
Const exception = "de,la"
Const delim = "-,'"


Dim e() As String
Dim d() As String
Dim i As Integer
    
    properCase = StrConv(str, vbProperCase)

    d = Split(delim, ",")
    For i = 0 To UBound(d)
        If InStr(str, d(i)) Then
            properCase = Replace(StrConv(Replace(str, d(i), " " & d(i) & " "), vbProperCase), " " & d(i) & " ", d(i))
        End If
    Next i
    
    e = Split(exception, ",")
    For i = 0 To UBound(e)
        If InStr(properCase, " " & e(i) & " ") Then
            properCase = Replace(properCase, " " & e(i) & " ", " " & e(i) & " ")
        End If
        
    Next i
   
End Function

examples
?propercase("mercedes-benz de la rue")
Mercedes-Benz de la Rue

?propercase("o'brien")
O'Brien