×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

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

Default Field Text Value to Proper (Sentence Case)
4

thread700-1670123

Default Field Text Value to Proper (Sentence Case)

Default Field Text Value to Proper (Sentence Case)

(OP)
I have a multi-user database environment where users are inputting free form data anyway they like. Some are using all caps, some don't capitalize at all, and others do. Problem is, when reports/labels, etc are printed out, this is an issue.

I would like to force the value of these field to be put in the proper case. I know the way to do this for upper and lower case in the field properties, but I can't seem to find a way to force the sentence case. Anyone out there know how to do this? I could use solutions that run on the table itself or on the form used to type all the information in. Not picky!!

Thanks!! :)

RE: Default Field Text Value to Proper (Sentence Case)

I'm not sure how you could set sentence case since it's near impossible to write code to allow characters like IBM to be uppercase while lowercasing other text.

You could write (or find on the internet) code for the after update event of your control that would capitalize the first character of sentences.

Train the users winky smile

CODE

SELECT *
FROM USERS
WHERE Trainable = True;

0 Rows Returned

Duane
Hook'D on Access
MS Access MVP

RE: Default Field Text Value to Proper (Sentence Case)

It would help to explain the fields/fields in question.  If this is some basic then you could propably write some code.  If as Duane points out, if it is complex, then coming up with some rules may be very difficult to get 100% solution.  This can be done but the rules can get very complicated with acronyms, and abbreviations, and proper names, etc.

So it may depend on the type of data and sentences. If your sentences look like this it could be really hard
mr. smith and mr. jones went to florida on saturday 21 feb. on TWA flight no. abc12 to visit XYZ ltd. and..


However maybe you could get an 85% solution and have the user verify.  So on before update check to see if all caps or all lower or if words following "." are lower. Then prompt the user to fix or you could also choose in the after update (if it is not proper) you could run a function to make everything lower case and then uppercase the first letter following a period and the very first letter.  Then prompt the user to verify the changes (acronyms and proper names and other pecularities they should fix.)

You could run this same function on existing data, and then you/admin can verify the changes.

I think the autocorrect on a memofield is very limited. It will capitalize the first letter of sentences (not the first sentence), but that is about it.

RE: Default Field Text Value to Proper (Sentence Case)

2
So this is a possible 85% solution. You could check if all upper case or all lower case then decide to fix or not.

CODE

Public Function isUcase(str As String) As String
   isUcase = (str = UCase(str))
End Function

Public Function isLcase(str As String) As String
   isLcase = (str = LCase(str))
End Function

This would change all to lower, then capitalize the first letter and everything following a period, ?,!.
Then you could store a huge list of special words (proper nouns) to fix and you could write another function for acronyms.

CODE

Public Function GetSentenceCase(str As String) As String
  Dim i As Integer
  Dim lttr As String
  Dim newStr As String
  Dim endFound As Boolean
  str = LCase(str)
  For i = 1 To Len(str)
    lttr = Mid(str, i, 1)
    If i = 1 Then lttr = UCase(lttr)
    If endFound And lttr <> " " Then
      lttr = UCase(lttr)
      endFound = False
    End If
    If lttr = "." Or lttr = "?" Or lttr = "!" Then endFound = True
     
    newStr = newStr & lttr
  Next i
  GetSentenceCase = ReplaceCommon(newStr)
End Function

Public Function ReplaceCommon(str As String) As String
  'you could store these in a table
  Dim varData() As Variant
  Dim itm As Variant
  'Examples you can add to
  varData = Array(" mr.", " mrs.", " monday", " tuesday", " wednesday", " jan ", " feb ", " st. ", " ave ")
  '.....
  For Each itm In varData
    str = Replace(str, itm, StrConv(itm, vbProperCase))
  Next itm
  ReplaceCommon = str
End Function

Using this as input

CODE

I HAVE A MULTI-USER DATABASE ENVIRONMENT WHERE USERS ARE INPUTTING FREE FORM DATA ANYWAY THEY LIKE. SOME ARE USING ALL CAPS, SOME DON'T CAPITALIZE AT ALL, AND OTHERS DO. PROBLEM IS, WHEN REPORTS/LABELS, ETC ARE PRINTED OUT, THIS IS AN ISSUE. I WOULD LIKE TO FORCE THE VALUE OF THESE FIELD TO BE PUT IN THE PROPER CASE. I KNOW THE WAY TO DO THIS FOR UPPER AND LOWER CASE IN THE FIELD PROPERTIES, BUT I CAN'T SEEM TO FIND A WAY TO FORCE THE SENTENCE CASE. ANYONE OUT THERE KNOW HOW TO DO THIS? I COULD USE SOLUTIONS THAT RUN ON THE TABLE ITSELF OR ON THE FORM USED TO TYPE ALL THE INFORMATION IN. NOT PICKY!! THANKS!!
Returns

CODE

I have a multi-user database environment where users are inputting free form data anyway they like. Some are using all caps, some don't capitalize at all, and others do. Problem is, when reports/labels, etc are printed out, this is an issue. I would like to force the value of these field to be put in the proper case. I know the way to do this for upper and lower case in the field properties, but i can't seem to find a way to force the sentence case. Anyone out there know how to do this? I could use solutions that run on the table itself or on the form used to type all the information in. Not picky!! Thanks!!
 

RE: Default Field Text Value to Proper (Sentence Case)

2
Something else you may want to check if the data is extensive grammar and spelling.

CODE

Public Function fcnCheckGrammerSpelling(strInfo As String) As String

    Dim wdApp As Object    'create Word object variable

    On Error GoTo fcnCheckGrammerSpelling_Error

    Set wdApp = CreateObject("Word.Application")

    wdApp.Visible = False
    wdApp.Documents.Add
    wdApp.Selection.Text = strInfo
    wdApp.ActiveDocument.CheckGrammar
    strInfo = wdApp.Selection.Text
    wdApp.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
    wdApp.Quit

    Set wdApp = Nothing
    fcnCheckGrammerSpelling = strInfo

    On Error GoTo 0
    Exit Function

fcnCheckGrammerSpelling_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fcnCheckGrammerSpelling of Module Module1"

End Function

Example of how to call from a form

CODE

Private Sub Command2_Click()
    Dim strRtnData As String
    Dim strToPass As String

    strToPass = Me.Text1.Value
    strRtnData = fcnCheckGrammerSpelling(strToPass)
    Me.Text1.Value = strRtnData
End Sub

Although this wont do anything for all caps it will fix everything else

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

RE: Default Field Text Value to Proper (Sentence Case)

That works well.

RE: Default Field Text Value to Proper (Sentence Case)

actually an easier way to call it would be

CODE

Text1.Value = fcnCheckGrammerSpelling(Text1.Value)

it works OK but I don't like the fact that the spell/grammar checker totally ignores a sentence in all caps. I'm thinking a combination of both solutions with a list of exceptions is the way to go the same way the spell checker in word uses a dictionary
 

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

RE: Default Field Text Value to Proper (Sentence Case)

ok how about

CODE

Text1.Value = fcnCheckGrammerSpelling(getSentenceCase(text1.value))

You could comment out
' GetSentenceCase = ReplaceCommon(newStr)
because the grammar checker would pick up all the special cases. That should be pretty close.

RE: Default Field Text Value to Proper (Sentence Case)

commented it out with no results. Changed

CODE

GetSentenceCase = ReplaceCommon(newStr)
to:

CODE

GetSentenceCase = newStr
Input:

CODE

I HAVE A MULTI-USER DATABASE ENVIRONMENT WHERE USERS ARE INPUTTING FREE FORM DATA ANYWAY THEY LIKE. SOME ARE USING ALL CAPS, SOME DON'T CAPITALIZE AT ALL, AND OTHERS DO. PROBLEM IS, WHEN REPORTS/LABELS, ETC ARE PRINTED OUT, THIS IS AN ISSUE. I WOULD LIKE TO FORCE THE VALUE OF THESE FIELD TO BE PUT IN THE PROPER CASE. I KNOW THE WAY TO DO THIS FOR UPPER AND LOWER CASE IN THE FIELD PROPERTIES, BUT I CAN'T SEEM TO FIND A WAY TO FORCE THE SENTENCE CASE. ANYONE OUT THERE KNOW HOW TO DO THIS? I COULD USE SOLUTIONS THAT RUN ON THE TABLE ITSELF OR ON THE FORM USED TO TYPE ALL THE INFORMATION IN. NOT PICKY!! THANKS!!

Output:

CODE

I have a multi-user database environment where users are inputting free form data anyway, they like. Some are using all caps, some do not capitalize at all, and others do. Problem is, when reports/labels, etc are printed out, this is an issue. I would like to force the value of these fields to the proper case. I know the way to do this for upper and lower case in the field properties, but I cannot seem to find a way to force the sentence case. Anyone out there knows how to do this? I could use solutions that run on the table itself or on the form used to type all the information in. Not fussy! Thanks!

Form code to handle Nulls and "" empty strings

CODE

Private Sub Command2_Click()
    If Me.Text1.Value = "" Or IsNull(Me.Text1) Then
        MsgBox "Please enter data to check"
        Exit Sub
    Else
        Text1.Value = fcnCheckGrammerSpelling(GetSentenceCase(Text1.Value))
        MsgBox "Grammar and spell check complete"
    End If
End Sub

nice work MajP :)

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

RE: Default Field Text Value to Proper (Sentence Case)

Well deserved stars to both MajP (Major Provider of great stuff) and  MazeWorX (AMazing Excellent Worx).

Duane
Hook'D on Access
MS Access MVP

RE: Default Field Text Value to Proper (Sentence Case)

Agreed!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
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