Default Field Text Value to Proper (Sentence Case) 4

[Bronte1226]

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

 

[dhookom]

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 ;-)

SELECT * 
FROM USERS
WHERE Trainable = True;

0 Rows Returned

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

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.

 

[MajP]

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

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.

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

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

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

 

[MazeWorX]

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

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

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

 

[MajP]

That works well.

 

[MazeWorX]

actually an easier way to call it would be

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

 

[MajP]

ok how about

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.

 

[MazeWorX]

commented it out with no results. Changed

GetSentenceCase = ReplaceCommon(newStr)

to:

 GetSentenceCase = newStr

Input:

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:

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

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

 

[dhookom]

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

Duane
Hook'D on Access
MS Access MVP

 

[jges]

Agreed!