Take the first letter of the name

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
 
D

Douglas J. Steele

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
 
T

Tom Ellison

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
 
S

Scott

Douglas & Tom,

Many thanks for your suggestions that work correctly to my issues.

Scott
 
Top