First Letter of Each Word

R

Rick_C

Help would be appreciated for the following...

In a report, I have a field named: CompanyName (in the DETAIL section of
the report)

I would like to be able to parse the first letter of each word.
Example: General Aviation Association Contractors would look like GAAC

Sometimes the string could be more or less than four words .

I created another field which I named: CompanyNameInitials

In the control source I wrote: =Left([CompanyName],1) which gives me the
initial of only the first word.

What can I write that would give me the first initial of all the words?

Any help would be appreciated.

Thank you in advance for any replies and assistance.

Rick
 
C

Craig Hornish

Quick solution

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intSpacePosition As Integer
Dim strCompanyName As String
Dim strCompanyInitials As String

If FormatCount = 1 Then
strCompanyName = Trim(Me.CompanyName)
intSpacePosition = 1
strCompanyInitials = Left(strCompanyName, 1)
intSpacePosition = InStr(intSpacePosition + 1, strCompanyName, " ")

Do While intSpacePosition <> 0
strCompanyInitials = strCompanyInitials &
Mid(Trim(strCompanyName), intSpacePosition + 1, 1)
intSpacePosition = InStr(intSpacePosition + 1, strCompanyName, "
")
Loop
Me.txtCompanyInitials = strCompanyInitials

End If

End Sub

Don't know if the If formatcount = 1 is needed but it doesnt hurt.

Craig Hornish
 
R

Rick_C

Craig:

Thanks for your response. I cut and pasted your code and I am getting the
following error:

Run-time error '6':

Overflow

Any help would be appreciated. Would it be easier if your could was a public
function module that I could enter into the control source as:
firstletteronly(([CompanyName])) ?

Thanks...

Rick


Craig Hornish said:
Quick solution

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intSpacePosition As Integer
Dim strCompanyName As String
Dim strCompanyInitials As String

If FormatCount = 1 Then
strCompanyName = Trim(Me.CompanyName)
intSpacePosition = 1
strCompanyInitials = Left(strCompanyName, 1)
intSpacePosition = InStr(intSpacePosition + 1, strCompanyName, " ")

Do While intSpacePosition <> 0
strCompanyInitials = strCompanyInitials &
Mid(Trim(strCompanyName), intSpacePosition + 1, 1)
intSpacePosition = InStr(intSpacePosition + 1, strCompanyName, "
")
Loop
Me.txtCompanyInitials = strCompanyInitials

End If

End Sub

Don't know if the If formatcount = 1 is needed but it doesnt hurt.

Craig Hornish




Rick_C said:
Help would be appreciated for the following...

In a report, I have a field named: CompanyName (in the DETAIL section of
the report)

I would like to be able to parse the first letter of each word.
Example: General Aviation Association Contractors would look like
GAAC

Sometimes the string could be more or less than four words .

I created another field which I named: CompanyNameInitials

In the control source I wrote: =Left([CompanyName],1) which gives me the
initial of only the first word.

What can I write that would give me the first initial of all the words?

Any help would be appreciated.

Thank you in advance for any replies and assistance.

Rick
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Probably not the most efficient code, but here is something I slapped together.
This is not fully tested, but it shoudl work.

Public Function getInitials(ByVal StrIn)
Dim StrReturn As String, strTemp As String
Dim iLoop As Integer
Select Case Len(Trim(StrIn & vbNullString))
Case 0
getInitials = StrIn

Case 1
getInitials = Left(Trim(StrIn), 1)

Case Else
StrIn = Trim(StrIn)
StrReturn = Left(StrIn, 1)
For iLoop = 1 To Len(StrIn)-1
If Mid(StrIn, iLoop, 1) = " " Then
StrReturn = Trim(StrReturn & Mid(StrIn, iLoop + 1, 1))
'Drop the trim above if you want to keep the spaces after
'Spaces (turn two Spaces into one space
End If
Next iLoop
getInitials = StrReturn
End Select
End Function
 
Top