Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here


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.


"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.
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:


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
         Ltr = UCase(Mid(Txt, idx, 1))
         If Ltr >= "A" And Ltr <= "Z" Then
            Mid(Txt, idx, 1) = Ltr
            Exit Do
            idx = idx + 1
         End If
      Loop Until idx >= Max
      idx = 1
         'idxStr = InStr(idx, Txt, ".")
         idxStr = NextIdx(Txt, idx, Max)
         If idxStr Then
               Ltr = UCase(Mid(Txt, idxStr, 1))
               If Ltr >= "A" And Ltr <= "Z" Then
                  Mid(Txt, idxStr, 1) = Ltr
                  idx = idxStr + 1
                  idxStr = Max
                  idxStr = idxStr + 1
                  idx = idxStr
               End If
            Loop Until idxStr >= Max
            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
      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
      NextIdx = 0
   End If

End Function
Finally  a few examples of calling the function:


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


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


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

Cheers! . . . thumbsup2

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

My Archive

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close