Short Name from Full Name

[bsJaber]

I have a field "FullName" and saving the data full name such as BISMILLAH SHAFIQE JABER. I want to create Short name from this full name such as B S JABER by query. How to do this.

 

[Beetle]

My first advice would be to store each element of the name in a separate field;

FirstName
MiddleName
LastName

Then you can simply display FullName in a query when needed with;

FullName: [FirstName] & " " & [MiddleName] & " " [LastName]

To display Short Name you would use;

ShortName: Left([FirstName], 1) & " " & Left([MiddleName], 1) & " " & [LastName]

As it is, with FullName stored in one field, you'll have to use a more complicated expression like;

ShortName: Left([FullName],1) & " " & Mid([Fullname],InStr(1,[FullName]," ")+1,1) & " " & Mid([FullName],InStrRev([FullName]," ")+1,Len([FullName])-InStrRev([FullName]," "))

 

[raskew]

Hi -

This can be done using a full-name field. Try playing with this:

x = "BISMILLAH SHAFIQE JABER"
y = left(x,1) & " " & mid(x, instr(x, " ")+1, 1) & mid(x, instrrev(x, " "))
? y
B S JABER

Here's a version of the instrrev() function:

Function InStrRev(ByVal tstr As String, twhat As String) As Integer
'*******************************************
'Purpose: Return location of last instance of a character or phrase.
'Coded by: raskew
'Inputs: ? InStrRev("the quick brown fox jumped the lazy dog", "the")
'Output: 28 - Location of last occurence of "the"
'*******************************************

Dim i As Integer, n As Integer, tLen As Integer

n = 0
tLen = Len(twhat)
For i = Len(RTrim(tstr)) To 1 Step -1

If Mid(tstr, i, tLen) = twhat Then
n = i
Exit For
End If
Next i

InStrRev = n

End Function

If, perchance, names are stored as LastName, FirstName MiddleName (a common practice in some environments) you can do this:

x = "JABER, BISMILLAH SHAFIQE"
Y = mid(x, instr(x, " ")+1,1) & mid(x, instrrev(x," "),2) & " " & Left(x, instr(x, " ")-2)
? y
B S JABER

Best wishes - Bob

 

[jdraw]

Here is another function that may be useful.

Please read the description carefully as the format is key to this function.

'---------------------------------------------------------------------------------------
' Procedure : ParseName
' Author    : Jack
' Created   : 2/23/2010
' Purpose   : To parse a field containing the person's full name and to return
' the first name, or the initial if it exists, or last name depending on the
' value of strWhich.
'
' NOTE: The format of the fullname field is
'       Lastname, Firstname Initial(may not be present)
' eg    a)De Jesus, Charlene K.
'       b)O'Sullivan, Margaret
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: strname == the person's fullname
'         strWhich = F  First Name
'                  = M  Middle Initial
'                  = L  Last Name
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function ParseName(strName As String, strWhich As String) As String

  
Dim strUtil As String
Dim strLastname As String
Dim strFirstname As String
Dim strMiddle As String
   On Error GoTo ParseName_Error

strUtil = Trim(strName)
strLastname = Left(strUtil, InStr(1, strUtil, ",") - 1)
strMiddle = Mid(strUtil, InStrRev(strUtil, " ") + 1)
    If Len(strMiddle) <> 1 Then
         strMiddle = vbNullString
    Else
        ParseName = strMiddle
        strUtil = Mid(strUtil, 1, Len(strUtil) - 2)
    End If
strFirstname = LTrim(Mid(strUtil, InStr(1, strUtil, ",") + 1))

Select Case strWhich
Case "F"
ParseName = strFirstname
Case "L"
ParseName = strLastname
Case "M"
ParseName = strMiddle
Case Else
ParseName = vbNullString
End Select


   On Error GoTo 0
   Exit Function

ParseName_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ParseName of Module Module4"
   
End Function

and here is a small test routine.

Sub jnames()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("TestNames")
Do While Not rs.EOF
   Debug.Print rs!FullName & " -**- " & ParseName(rs!FullName, "F") &      ParseName(rs!FullName, "M") & " " & Trim(ParseName(rs!FullName, "L"))
rs.MoveNext
Loop
End Sub

Here is the data in the TestNames table

id FullName
1 Smits,Jonathan P.
2 Payne,Ima
3 del la Hoya,Oscar
4 O'Brien,Megan M.
5 Van't Goor,Ali
6 De Jesus,Sheilaugh
7 Del Carrico,Efriam O.
8 O'Sullivan,Sean
9 D'Ata Orallia,Charlene K.

And here is the output from the test procedure

Smits,Jonathan P. -**- Jonathan P. Smits
Payne,Ima -**- Ima Payne
del la Hoya,Oscar -**- Oscar del la Hoya
O'Brien,Megan M. -**- Megan M. O'Brien
Van't Goor,Ali -**- Ali Van't Goor
De Jesus,Sheilaugh -**- Sheilaugh De Jesus
Del Carrico,Efriam O. -**- Efriam O. Del Carrico
O'Sullivan,Sean -**- Sean O'Sullivan
D'Ata Orallia,Charlene K. -**- Charlene K. D'Ata Orallia

 

[bsJaber]

My first advice would be to store each element of the name in a separate field;

FirstName
MiddleName
LastName

Then you can simply display FullName in a query when needed with;

FullName: [FirstName] & " " & [MiddleName] & " " [LastName]

To display Short Name you would use;

ShortName: Left([FirstName], 1) & " " & Left([MiddleName], 1) & " " & [LastName]

As it is, with FullName stored in one field, you'll have to use a more complicated expression like;

ShortName: Left([FullName],1) & " " & Mid([Fullname],InStr(1,[FullName]," ")+1,1) & " " & Mid([FullName],InStrRev([FullName]," ")+1,Len([FullName])-InStrRev([FullName]," "))

Sir thanks for help but there is problem using this command, it is only given correct result when the field have full name such as BISMILLAH SHAFIQE JABER when i have on single name such as BISMILLAH it is converted as B B BISMILLAH, it should be remain same BISMILLAH

 

[Beetle]

Yes, that adds another layer of complexity to it, which is further evidence of why it should not be stored this way. How many possible name elements are there in this field?

1 (BISMILLAH)?
2 (BISMILLAH JABER)?
3 (BISMILLAH SHAFIQE JABER)?
4 (JOHN JANGLE JINGLEHEIMER SCHMIDT) or more?

 

[raskew]

Hi -

Here's a function that will recreate a short-name, regardless of the number of middle names:

Public Function Shortname(x As String) As String
'Purpose: Reduce long name to initials and last name
'Re: http://www.access-programmers.co.uk/forums/showthread.php?t=224619
'Coded by: raskew
'************************************************
Dim y As String, n As Integer, p As Integer

y = ""
p = Len(x) - Len(Replace(x, " ", ""))
For n = 1 To p
y = y & Left(x, 1) & " "
x = Trim(Mid(x, InStr(x, " ")))
Next n
Shortname = y & x

End Function

'************************************************
Examples:

? shortname("BISMILLAH")
BISMILLAH

? shortname("BISMILLAH JABER")
B JABER

? shortname("BISMILLAH SHAFIQUE JABER")
B S JABER

? shortname("JOHN JANGLE JINGLEHEIMER SCHMIDT")
J J J SCHMIDT

? shortname("Huey Louie Dewy Alpha Bravo Charlie Jones")
H L D A B C Jones

Best wishes - Bob

 

[jdraw]

How does it work for David del Rio? Where del Rio is his last name. Or Family names like D'Ata Orallia and someone like Oscar del la Hoya.

This isn't straight forward; is often dependent on input/storage format; and many functions exist trying to deal with these various hyphenated, compound, quote including Family Names.