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!! :)
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)
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
CODE
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)
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)
CODE
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
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
CODE
RE: Default Field Text Value to Proper (Sentence Case)
CODE
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
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)
RE: Default Field Text Value to Proper (Sentence Case)
CODE
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)
CODE
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)
CODE
CODE
CODE
Output:
CODE
Form code to handle Nulls and "" empty strings
CODE
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)
Duane
Hook'D on Access
MS Access MVP
RE: Default Field Text Value to Proper (Sentence Case)