Solved Calculating initials (1 Viewer)

[arnelgp]

Public Function reorderText(ByVal textString As Variant) As String
Dim var As Variant, i As Integer
textString = textString & vbNullString
reorderText = textString
If Len(textString) < 1 Then Exit Function
Do Until InStr(1, textString, "  ") = 0
    textString = Replace$(textString, "  ", " ")
Loop
var = Split(textString, " ")
Select Case UBound(var)
Case Is > 1
    If HaveJr(var(UBound(var))) Then
        textString = var(UBound(var) - 1) & " " & var(UBound(var)) & ", "
        For i = 0 To UBound(var) - 2
            textString = textString & var(i) & " "
        Next
    Else
        textString = var(UBound(var)) & ", "
        For i = 0 To UBound(var) - 1
            textString = textString & var(i) & " "
        Next
    End If
    textString = Trim$(textString)
Case Is = 1
    textString = var(1) & ", " & var(0)
End Select
reorderText = textString
End Function

Public Function HaveJr(ByVal p As String) As Boolean
Const t As String = "/jr/sr/i/ii/iii/iv/v/vi/vii/viii/ix/x/xi/xii/xii/xiv/xv/"
If Len(p) < 1 Then Exit Function
p = Replace$(p, ".", "")
HaveJr = t Like "*/" & p & "/*"
End Function

 

[theDBguy]

Ps. Haven't spent much time perfecting as it is only required to make all fields consistent and for sorting purposes.

Hi. Pardon me for jumping in, but if you're only trying to sort the data, then you don't really need to flip the text around. You could just try something like:

...ORDER BY Mid([FieldName], InStrRev([FieldName], " ") +1)

 

[Kayleigh]

Cheers @MajP Works fab!!
@arnelgp - your code also does the job but seems to include a lot more checks than I actually need. Though it looks as if you quite enjoyed incorporating more code for the challenge of it - even if it seems overkill in my circumstance - may come in useful in future

@theDBguy - You are correct but I will be doing a UNION query with another field which is in format I am seeking hence uniformity necessary.

 

[MajP]

One thing @arnelgp is checking is if you have (Jr, Sr, II, III etc
John Smith Jr.
John Smith III
My code will give you
Jr., John Smith
which is not what you want. This may or may not be an issue for your data, but beware if it is then you need to use @arnelgp.