Lowercase last names

[gojets1721]

Curious if someone could help with this issue. Couldn't find a specific guide online to assist.

I have a table of last names that are entirely uppercase. I want make them proper text. I'm using the following function in an update function that works well:

StrConv ( [CustomerLastName], 3 )

The problem is I want it recognize apostrophes and dashes, and capitalize the succeeding letter. See the below table for examples:

Before Update​	After Update​	Ideal​
O'BRYAN	O'bryan	O'Bryan
SMITH-JONES	Smith-jones	Smith-Jones

Is there a way to add onto to / change this code so that any letter after an '-' or an ' ' ' is also capitalized?

 

[mike60smart]

Try

StrConv(CustomerLastName, vbProperCase) 'convert to proper case

 

[ebs17]

Look at that:

Sub words()
    Dim sText As String
    Dim oMC As Object
    Dim i As Long
    Dim sResult As String

    sText = "O'BRYAN, SMITH-JONES"
    Set oMC = RegExMatchCollection(sText, "(\b.+?\b)")
    If oMC.Count > 0 Then
        For i = 0 To oMC.Count - 1
            'Debug.Print oMC(i)
            sResult = sResult & StrConv(oMC(i), 3)
        Next
    End If
    Debug.Print sResult
End Sub

'------------------------------------------------------------------------
' Return
O'Bryan, Smith-Jones

Link to RegExMatchCollection

 

[Pat Hartman]

@mike60smart vbProperCase IS 3. In VBA, I would always use the enumeration but in a query, you need to use the number.

 

[gojets1721]

Look at that:

Sub words()
    Dim sText As String
    Dim oMC As Object
    Dim i As Long
    Dim sResult As String

    sText = "O'BRYAN, SMITH-JONES"
    Set oMC = RegExMatchCollection(sText, "(\b.+?\b)")
    If oMC.Count > 0 Then
        For i = 0 To oMC.Count - 1
            'Debug.Print oMC(i)
            sResult = sResult & StrConv(oMC(i), 3)
        Next
    End If
    Debug.Print sResult
End Sub

'------------------------------------------------------------------------
' Return
O'Bryan, Smith-Jones

Link to RegExMatchCollection

Thanks. How would I incorporate this into an update query?

 

[MajP]

Public Function ProperCaseEBS(sText As Variant) As String
   If Not IsNull(sText) Then
      Dim oMC As Object
      Dim i As Long
      Dim sResult As String
      Set oMC = RegExMatchCollection(sText, "(\b.+?\b)")
       If oMC.Count > 0 Then
           For i = 0 To oMC.Count - 1
            sResult = sResult & StrConv(oMC(i), 3)
        Next
      End If
      ProperCaseEBS = sResult
   End If
End Function

Update SomeTable SET CustomerLastName = ProperCaseEBS([CustomerLastName])