INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Microsoft: Access Forms FAQ

## How to

Capitalize first letter in sentences in a string by TheAceMan1
Posted: 16 Jan 07 (Edited 18 Jan 07)

Howdy All! . . .

The following function capitalizes the first letter in each sentence of a string. The effect is the same as capitalizing the first letter of the first word.

Example:

"now is. the time.  for all good men." & vbNewLine & vbnewline & "to come to the aid of their countrymen."
results in:
Now is. The time.  For all good men.

To come to the aid of their countrymen.

Be aware, if a sentence doesn't start with text its still the first letter thats capitalized:

133202qk is a serial number.
returns:
133202Qk is a serial number.

Also the following sentence termination characters are incorperated . ? ! : ... more can be added in the NextIdx function.

Copy/paste the following to a new module in the modules window. Name the module modCapSentences:

#### CODE

Public Function CapSentences(ByVal Txt As String) As String
Dim idx As Long, idxStr As Long, Max As Long, Ltr As String

DoCmd.Hourglass True
idx = 1
Max = Len(Txt)

If Txt <> "" Then
Do
Ltr = UCase(Mid(Txt, idx, 1))

If Ltr >= "A" And Ltr <= "Z" Then
Mid(Txt, idx, 1) = Ltr
Exit Do
Else
idx = idx + 1
End If
Loop Until idx >= Max

idx = 1

Do
'idxStr = InStr(idx, Txt, ".")
idxStr = NextIdx(Txt, idx, Max)

If idxStr Then
Do
Ltr = UCase(Mid(Txt, idxStr, 1))

If Ltr >= "A" And Ltr <= "Z" Then
Mid(Txt, idxStr, 1) = Ltr
idx = idxStr + 1
idxStr = Max
Else
idxStr = idxStr + 1
idx = idxStr
End If
Loop Until idxStr >= Max
Else
idx = Max
End If
Loop Until idx >= Max

CapSentences = Txt
End If

DoCmd.Hourglass False

End Function

Public Function NextIdx(Txt As String, idxLast As Long, Max As Long) As Long
Dim x As Integer, idxBest As Long, idx As Long

x = 1
idxBest = Max

Do
idx = InStr(idxLast, Txt, Choose(x, ".", "?", "!", ":", "..."))
If idx <> 0 And idx < idxBest Then idxBest = idx

x = x + 1
Loop Until x > 5

If idxBest < Max Then
NextIdx = idxBest
Else
NextIdx = 0
End If

End Function
Finally  a few examples of calling the function:

#### CODE

Me!TextboxName = "sentence one. sentence 2."
Me!TextboxName = CapSentences(Me!TextboxName)

#### CODE

variablename = "sentence one. sentence 2."
variablename = CapSentences(variablename)

#### CODE

=CapSentences("sentence one. sentence 2.")

Cheers! . . .

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!