Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    force mixed case in textbox (2002)

    How can I format the data entered by a user in a textbox on a form to be mixed (or Proper) case? If I should do this using the StrConv function, in which event does it make the most sense to do it?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: force mixed case in textbox (2002)

    You can do this in the After Update event of the text box:

    Private Sub txtLastName_AfterUpdate()
    Me.txtLastName = StrConv(Me.txtLastName, vbProperCase)
    End Sub

    StrConv is not an intelligent function. If you enter "mccartney", it will be converted to "Mccartney", not "McCartney". There are custom functions that take special cases like this into account. Holler if you need that.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: force mixed case in textbox (2002)

    Thanks, Hans. If you could point me towards one of those custom functions, I'd appreciate it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: force mixed case in textbox (2002)

    Here is a simple example originally from Microsoft. It uses DAO, so you should set a reference to the Microsoft DAO 3.6 Object Library in Tools | References... (in the Visual Basic Editor.)

    Create a table Names with a single text field NewName that stores exceptions. For example:

    <table border=1><td>NewName</td><td>BA</td><td>BS</td><td>DC</td><td>de</td><td>der</td><td>DO</td><td>II</td><td>III</td><td>IV</td><td>IX</td><td>le</td><td>MacDonald</td><td>McCloud</td><td>McDonald</td><td>McEwan</td><td>McLeod</td><td>McQueen</td><td>MD</td><td>PhD</td><td>van</td><td>VI</td><td>VII</td><td>VIII</td><td>XI</td><td>XII</td></table>
    You can add as many items as you like.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Function ProperLookup(ByVal InText)
    ' Similar to Proper(), but uses a table (Names) to look up words that don't fit the general formula.
    Dim OutText As String
    Dim Word As String
    Dim i As Integer
    Dim c As String
    Dim rst As DAO.Recordset
    ' Output Null and other non-text as is
    If VarType(InText) <> vbString Then
    ProperLookup = InText
    Else
    Set rst = CurrentDb.OpenRecordset("Names", dbOpenTable)
    rst.Index = "PrimaryKey"
    For i = 1 To Len(InText)
    c = Mid$(InText, i, 1)
    Select Case c
    Case "A" To "Z"
    ' If text, then build word
    Word = Word & c
    Case Else
    If Word <> "" Then
    ' If not, then append existing word and then the character
    rst.Seek "=", Word
    If rst.NoMatch Then
    Word = UCase$(Left$(Word, 1)) & LCase$(Mid$(Word, 2))
    Else
    Word = rst!NewName
    End If
    OutText = OutText & Word
    Word = ""
    End If
    OutText = OutText & c
    End Select
    Next i
    ' Process final word
    If Word <> "" Then
    rst.Seek "=", Word
    If rst.NoMatch Then
    Word = UCase(Left$(Word, 1)) & LCase$(Mid$(Word, 2))
    Else
    Word = rst!NewName
    End If
    OutText = OutText & Word
    End If
    ' Close table and return result
    rst.Close
    Set rst = Nothing
    ProperLookup = OutText
    End If
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    <!profile=MarkD>MarkD<!/profile> posted a much more elaborate function not very long ago; perhaps he can point you to his post.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: force mixed case in textbox (2002)

    Thanks! I'll look for MarkD's post, but your function will do the trick, I'm sure.

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: force mixed case in textbox (2002)

    I posted some code recently that involved parsing names and initials but not sure if that's applicable here. If interested this is a custom "Proper Case" function that will handle some, but not all, exceptions:

    Function GetProperCase(ByVal strTxt As String) As String

    ' This function has been modified from original (source unknown)!!
    Dim n As Integer
    Dim intChar As Integer

    'First convert to Proper Case:
    strTxt = StrConv(Trim$(strTxt), vbProperCase)

    ' Exclude last character in string:
    For n = 1 To Len(strTxt) - 1
    intChar = Asc(Mid$(strTxt, n, 1))
    Select Case intChar
    Case 32 ' space - some exceptions (others can be added):
    Select Case Mid$(strTxt, n + 1, 3)
    ' This may not always apply:
    Case "da ", "de ", "di "
    Mid(strTxt, n + 1, 1) = LCase$(Mid$(strTxt, n + 1, 1))
    Case Else
    Mid(strTxt, n + 1, 1) = UCase$(Mid$(strTxt, n + 1, 1))
    End Select

    Case 38, 39, 45, 46, 47 ' '& , ' , - , . , /
    ' convert the character after symbol to uppercase:
    ' Original code (hard way):
    ' strTxt = Left$(strTxt, n) & UCase$(Mid$(strTxt, n + 1, 1)) & Mid$(strTxt, n + 2, 255)
    ' Revised code (simple way):
    Mid(strTxt, n + 1, 1) = UCase$(Mid$(strTxt, n + 1, 1))

    Case 99 ' c
    ' McAfee's, McDonald's, etc
    If Mid$(strTxt, n - 1, 1) = "M" Then
    ' Capitalize next letter after "c":
    Mid(strTxt, n + 1, 1) = UCase$(Mid$(strTxt, n + 1, 1))
    End If

    End Select
    Next

    GetProperCase = strTxt

    End Function

    Note use of Mid statement to replace a single character with an upper case character, this is a lot simpler (and efficient) than a bunch of Left, Mid, & Right functions concatenated together. Also testing single character as number (Asc function) in Select Case statements is faster, but maybe less intuitive? As noted, this function will handle some cases "properly" but not all. Examples:

    ? GetProperCase("john a. smith, jr. ")
    John A. Smith, Jr.

    ? GetProperCase("john mcdonald, m.d.")
    John McDonald, M.D.
    But:
    ? GetProperCase("john macdonald, md")
    John Macdonald, Md

    ? GetProperCase("leonardo da vinci")
    Leonardo da Vinci
    ? GetProperCase("charles de gaulle")
    Charles de Gaulle
    ? GetProperCase("jack o'brien")
    Jack O'Brien

    I don't know of any foolproof way to distinguish names like Mack or MacDonald, Vanderbilt or VanDerGraaf, etc. You'd probably have to use an exceptions table of some kind. Or make sure names are entered correctly in first place.

    HTH

  7. #7
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: force mixed case in textbox (2002)

    thanks mark!

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: force mixed case in textbox (2002)

    The other approach for this problem (which HansV alluded to earlier) is to use an "Exception" table. If any words in text string match any words listed in Exception table (using non-case sensitive comparison), the word is replaced with the properly formatted word from table. If interested, example of how this works:

    Public Function GetProperCaseRev(ByVal strTxt As String) As String

    Dim tmp As Variant
    Dim strTest As String
    Dim n As Long
    Dim intPos As Integer

    'Trim any leading/trailing spaces & convert to Proper Case:
    strTxt = StrConv(Trim$(strTxt), vbProperCase)

    ' Split text into an array (assumes single space separates words):
    tmp = Split(strTxt, Chr$(32), , vbBinaryCompare)

    ' Use ADO GetString method to get test string from exception table:
    strTest = GetStringFromRst("Field1", "tblProperCase") & Chr$(32)

    'Test each item (word) in array for match:
    For n = 0 To UBound(tmp)
    ' Non-case sensitive comparison (add space to avoid false matches):
    intPos = InStr(1, strTest, tmp(n) & Chr$(32), vbTextCompare)
    If intPos > 0 Then
    ' Replace word with corresponding text from exception table:
    tmp(n) = Mid$(strTest, intPos, Len(tmp(n)))
    End If
    Next n

    ' When test & replace done, use Join function to convert array back to space-delimited text string:
    GetProperCaseRev = Join(tmp, Chr$(32))
    Erase tmp

    End Function

    To generate a "test string" from the Exception table, use GetString method with an ADO recordset opened on table:

    Public Function GetStringFromRst(ByRef strFld As String, _
    ByRef strTbl As String) As String
    On Error GoTo Err_Handler

    Dim rst As New ADODB.Recordset
    Dim strMsg As String
    Dim strSQL As String

    strSQL = "SELECT <!t>[" & strFld & "]<!/t> FROM <!t>[" & strTbl & "]<!/t> ORDER BY <!t>[" & strFld & "]<!/t>;"
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

    ' ADO GetString method:
    ' If ColumnDelimiter not specified, TAB is used
    ' If RowDelimiter not specified, CR is used
    ' Note the "Delimiter" args spelled wrong in function definition!!
    ' But spelled correctly in Help...
    GetStringFromRst = rst.GetString(StringFormat:=adClipString, _
    NumRows:=-1, _
    ColumnDelimeter:=vbNullString, _
    RowDelimeter:=Chr$(32))
    rst.Close

    Exit_Sub:
    Set rst = Nothing
    Exit Function
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "GET STRING ERROR MESSAGE"
    Resume Exit_Sub
    End Function

    As noted above, check the flaky spelling for "Delimiter" arguments for GetString if using named arguments with this function. The GetProperCaseRev function basically "splits" text string into an array, then compares each item to test string. If any matches, word is replaced with equivalent from Exception table. Join function used to convert array back to text string. Of course you have to populate the Exception table (here called "tblProperCase"). Some examples, using the test string shown below:

    ? GetStringFromRst("Field1","tblProperCase")
    da de di du FitzPatrick La Le MacDonald McDonald MD Ph.D. PhD Van VanDer von

    ? GetProperCaseRev("john a. mcdonald jr.")
    John A. McDonald Jr.
    ? GetProperCaseRev("leonardo da vinci")
    Leonardo da Vinci
    ? GetProperCaseRev("mr. patrick f. fitzpatrick, phd")
    Mr. Patrick F. Fitzpatrick, PhD

    For maximum flexibility you could combine approach used in previous reply with this method. Note, if converting a large number of records, you should call the GetStringFromRst function only once to get the test string, rather than repeatedly opening & closing recordset.

    HTH

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: force mixed case in textbox (2002)

    Great Code Mark.

    This just saved me alot of excess work as I had to modify imported text that was all uppercase to "proper" case. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •