Initials From Single Field

[byeo]

I am trying to get initials from a generic [Name] field - is there any way to
accomplish with a function? The field stores company names so if it had
Allied Waste Services in the field I want to return AWS.

Thank you

 

[KARL DEWEY]

This is a start ---
Left([YourField],1) & Right(Left([YourField],InStr([YourField]," ")+1),1)

But you run in to problems where there are three names and if the name has
spaces such as S. De La Renzo and Company. What about names with dashes?

 

[John Spencer]

Access 2000 or later

UNTESTED PUBLIC FUNCTION. Paste this into a module and call it in your
query.
Field: Intitials: fGetInitials([Company Name])

Public Function fGetInitals(strIN)
Dim aStr() as Variant
Dim I as Long
Dim sReturn as String

If Len(strIn & "") = 0 Then
fGetInitials = strIN
Else
aStr() = Split(strIn," ")
For I = Lbound(aStr) to UBound(aStr)
sReturn = sReturn & Left(aStr(i),1)
Next I
fGetInitials = sReturn
End if

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[byeo]

That works but like you say has some limits. Ideally I would want to return
the first letter of every new word regardless of how many words made up the
name. I was planning to remove dashes and other non-alpha characters.

It gives me something to start with as you say - thanks!

This is a start ---
Left([YourField],1) & Right(Left([YourField],InStr([YourField]," ")+1),1)

But you run in to problems where there are three names and if the name has
spaces such as S. De La Renzo and Company. What about names with dashes?

--
KARL DEWEY
Build a little - Test a little

I am trying to get initials from a generic [Name] field - is there any way to
accomplish with a function? The field stores company names so if it had
Allied Waste Services in the field I want to return AWS.

Thank you

 

[byeo]

Thanks John - I have a question that will definitely show my skill level -
how do I call the module from my query...

SELECT [APVendFlatFile_Build_II].System,
[APVendFlatFile_Build_II].VendorNumber, [APVendFlatFile_Build_II].VendorName
FROM [APVendFlatFile_Build_II];

I just want to add a field that displays the result of your function being
run on the field [VendorName].

Access 2000 or later

UNTESTED PUBLIC FUNCTION. Paste this into a module and call it in your
query.
Field: Intitials: fGetInitials([Company Name])

Public Function fGetInitals(strIN)
Dim aStr() as Variant
Dim I as Long
Dim sReturn as String

If Len(strIn & "") = 0 Then
fGetInitials = strIN
Else
aStr() = Split(strIn," ")
For I = Lbound(aStr) to UBound(aStr)
sReturn = sReturn & Left(aStr(i),1)
Next I
fGetInitials = sReturn
End if

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am trying to get initials from a generic [Name] field - is there any way
to
accomplish with a function? The field stores company names so if it had
Allied Waste Services in the field I want to return AWS.

Thank you

 

[John Spencer]

If you've pasted the module into a vba module and saved it (don't name the
module the same as the function), then your SQL statement should look
something like:

SELECT [APVendFlatFile_Build_II].System,
[APVendFlatFile_Build_II].VendorNumber
, [APVendFlatFile_Build_II].VendorName

, fGetInitials([APVendFlatFile_Build_II].[VendorName]) as Initials

FROM [APVendFlatFile_Build_II];


I found some typos in the function. Here is a revised version that should
work.

Public Function fGetInitials(strIN)
Dim aStr As Variant
Dim I As Long
Dim sReturn As Variant

If Len(strIN & "") = 0 Then
fGetInitials = strIN
Else
aStr = Split(strIN, " ")
For I = LBound(aStr) To UBound(aStr)
sReturn = sReturn & Left(aStr(I), 1)
Next I
fGetInitials = sReturn
End If

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John - I have a question that will definitely show my skill level -
how do I call the module from my query...

SELECT [APVendFlatFile_Build_II].System,
[APVendFlatFile_Build_II].VendorNumber,
[APVendFlatFile_Build_II].VendorName
FROM [APVendFlatFile_Build_II];

I just want to add a field that displays the result of your function being
run on the field [VendorName].

Access 2000 or later

UNTESTED PUBLIC FUNCTION. Paste this into a module and call it in your
query.
Field: Intitials: fGetInitials([Company Name])

Public Function fGetInitals(strIN)
Dim aStr() as Variant
Dim I as Long
Dim sReturn as String

If Len(strIn & "") = 0 Then
fGetInitials = strIN
Else
aStr() = Split(strIn," ")
For I = Lbound(aStr) to UBound(aStr)
sReturn = sReturn & Left(aStr(i),1)
Next I
fGetInitials = sReturn
End if

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am trying to get initials from a generic [Name] field - is there any
way
to
accomplish with a function? The field stores company names so if it
had
Allied Waste Services in the field I want to return AWS.

Thank you

 

[John W. Vinson]

I just want to add a field that displays the result of your function being
run on the field [VendorName].

Just the way he told you:

Paste this into a module and call it in your
query.
Field: Intitials: fGetInitials([Company Name])

That is, in a vacant Field cell in the query grid type

Intitials: fGetInitials([VendorName])

The name of this calculated field will be Initials - you can replace Initials
with any other name you want (VendorInitials perhaps).

John W. Vinson [MVP]