Take the first letter of the name

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I need to take the first letter of the name as below:-


John William -> JW

Can someone advise how to do it in query.

Thanks,

Scott
 
Since you can't be sure how many words will be in the name (think Billy Dee
Williams or Mary Tyler Moore), you'll probably want to write a function to
do it. Something like the following untested air-code:

Function GetInitials(FullName As String) As String
Dim lngLoop As Long
Dim strInitials As String
Dim varNames As Variant

varNames = Split(FullName, " ")
If IsNull(varNames) = False Then
For lngLoop = LBound(varNames) To UBound(varNames)
strInitials = strInitials & Left(varNames(lngLoop), 1)
Next lngLoop
End If

GetInitials = strInitials

End Function
 
Dear Scott:

SELECT Left(Address, 1)
& IIf(instr(address, " ") > 0,
Mid(address, instr(address, " ") + 1, 1), "")
AS YourColumn
FROM YourTableName

You may require more. Do you want middle initials?

The specification for this is to find the first letter of the first two
words. A word is defined as starting at the beginning of the column and
after the first space in the column.

There are things that would "fool" this:

- the column starts with a space (you could trim to avoid this)

- there are two spaces between words (many such complexities would move this
into the realm where it's better to write your own function to fully perform
this.)

- What do you want for O'Neal or McKinney? O and M?

Tom Ellison
 
Back
Top