Initials From Single Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
 
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
..
 
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!
KARL DEWEY said:
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


byeo said:
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
Click to expand...
Click to expand...
 
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].

John Spencer said:
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 said:
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
Click to expand...
Click to expand...
 
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
..

byeo said:
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].

John Spencer said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
I just want to add a field that displays the result of your function being
run on the field [VendorName].
Click to expand...

Just the way he told you:
Paste this into a module and call it in your
query.
Field: Intitials: fGetInitials([Company Name])
Click to expand...

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]
 
Back
Top