Access VBA

Last Name First

We also have a dozen or so entities that are an organization name that should be in the report rather then a person’s name.

Function LastNameFirst(Contact As LongPtr) As String
    Dim TheName     As String
    Dim daQuery     As Recordset
    Dim daSuff      As String
    Dim QUO         As String
    Dim daHonor     As String
    Dim Suffixer    As String
    Dim strSQL      As String
    QUO = Chr(34)
    strSQL = "SELECT * FROM NamesMaster WHERE ID=" & Contact

    Set daQuery = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    Do Until daQuery.EOF
        With daQuery
        If !ID = Contact Then
            If IsNull(!LastName) Then    'Moved to top 8/28/20
                TheName = !Company
                Exit Do
            Else
            If Len(!Prefix) > 0 Then
               daHonor = !Prefix & Chr(32)
            Else
                daHonor = ""
            End If

            TheName = RTrim(![LastName])
                If Len(![FirstName]) <= 1 Then
                    LastNameFirst = TheName
                    Exit Function
                End If
            TheName = TheName & ", " & daHonor & Chr(32) & ![FirstName] & Chr(32) & ![MiddleInit]
            End If
            If Len(!Suffix) > 0 Then
                daSuff = !Suffix
                TheName = TheName & ", " & daSuff
            End If
            Exit Do
        End If
resumeHere:
        End With
    daQuery.MoveNext
    Loop
    LastNameFirst = TheName     'Data from search
    daQuery.Close

End Function

 '*************************************************************

Function FirstNameFirst(Contact As LongPtr) As String
    Dim TheName     As String
    Dim daQuery     As Recordset
    Dim daSuff      As String
    Dim daHonor     As String
    Dim MInit       As String
    Dim Suffixer    As String
    On Error GoTo ErrHandle
    Set daQuery = CurrentDb.OpenRecordset("NamesMaster", dbOpenDynaset, dbSeeChanges)
    Do Until daQuery.EOF
        With daQuery
        If !ID = Contact Then
            If Len(!Company) > 0 Then
                TheName = !Company
                GoTo Company
            End If
            If Len(!Prefix) > 0 Then
                daHonor = !Prefix & Chr(32)
            Else
                daHonor = ""
            End If
            If Len(!MiddleInit) > 0 Then
                MInit = Trim(!MiddleInit) & Chr(32)
            Else
                MInit = ""
            End If
            TheName = ![LastName]
                If Len(![FirstName]) <= 1 Then
                    FirstNameFirst = TheName
                    Exit Function
                End If
                If ![MiddleInit] = Null Then
                    TheName = daHonor & ![FirstName] & TheName
                Else
                    TheName = daHonor & ![FirstName] & Chr(32) & MInit & TheName
                End If
            If Len(!Suffix) > 0 Then
                daSuff = !Suffix    '03/05/2020
                TheName = TheName & ", " & daSuff
            End If
            Exit Do
        End If
        End With
    daQuery.MoveNext
    Loop
Company:
    FirstNameFirst = TheName      'Data from search
    
    daQuery.Close
    Exit Function
ErrHandle:
End Function

I also had a database where someone else using it had chosen the prefix; whereas my initial use was to choose Dr or Rev to note a special prefix. Not every report needs the Mr (mister).