Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35

    Proper Case and Exceptions

    Hello,


    I have an application with fields where we establish Proper Case -

    '----------------------------
    'Converts Text to Proper Case
    '----------------------------
    Public Sub ConvertToProper()
    'Test if control contains text
    If IsNull(Screen.ActiveControl) = False Then
    'convert text to Proper Case
    Screen.ActiveControl = StrConv(Screen.ActiveControl, vbProperCase)
    End If
    End Sub

    However, in doing this, we get some changes we do not want. Example, company abbreviations like KDB, KCPL, XYZ become Kdb, Kcpl and Xyz. Even State abbreviations like MO are converted to Mo. I have tried to create exceptions in the Proofing section of the database, but that does not work. Is there a way to create exceptions to this, or set it up correctly in the system settings.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    AFAIK, you would need a table of exceptions unless you can devise a logic (rule) for all cases. Or you would need additional fields things like abbreviations, names that are not real words, and apostrophes; e.g. O'Brien - assuming you would allow that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with micron. Access won't do anything you don't tell it. So you need an algorithm/logic or some other method to deal with your exception cases. It is a common issue.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try thiis (untested). It should retain all values such as KFC that are fully upper case

    Code:
    '----------------------------
    'Converts Text to Proper Case
    '----------------------------
    Public Sub ConvertToProper()
    'Test if control contains text and isn't all upper case
    If Not IsNull(Screen.ActiveControl) And Screen.ActiveControl<>UCase(Screen.ActiveControl) Then
    'convert text to Proper Case
    Screen.ActiveControl = StrConv(Screen.ActiveControl, vbProperCase)
    End If
    End Sub
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Wondering if you really need 2 tests? If it is Null, it is not UCase either?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    Would you run this code for the Text Box as an "After Update" event?

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Screen.ActiveControl<>UCase(Screen.ActiveControl)
    since Access is not case sensitive think you will need to do a binary comparison


    strcomp(Screen.ActiveControl,ucase(Screen.ActiveCo ntrol),0)


    e.g.

    ?strcomp("ABC",ucase("ABC"),vbBinaryCompare)
    0
    ?strcomp("abc",ucase("abc"),vbBinaryCompare)
    1

    but won't solve other issues as suggested by Micron

  8. #8
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    I found this in another thread about creating an exception table and then checking the field for exceptions, and it seems to work however, it only checks the first word. My field will have several words in it, such as "Deliver to KCPL site in Sibley MO". Or Rock will be delivered by KDB Hauling. Is there a way in this code to expand out and check all the words in the text box?

    Follow these instructions and you should be all set.

    Make a new table.
    Field: [ID] AutoNumber Indexed No Duplicates
    Field: [ExceptName] Text
    TableName: tblExceptions

    Enter as many known name exceptions as you can.

    ====
    Copy and Paste this function into a new module.
    *** Some of the longer lines may be incorrectly wrapped due to your
    mail reader.***

    Public Function ConvExceptions(StringIn As String) As String

    ' Will find exceptions to Proper Case capitalization of names.

    On Error Resume Next

    If DCount("*", "tblExceptions", "[ExceptName] = " & Chr(34) & StringIn
    & Chr(34) & "") > 0 Then
    Dim intResponse As Integer
    Dim strFind As String
    strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =
    " & Chr(34) & StringIn & Chr(34) & "")
    intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
    vbCrLf & " Accept the above capitalization? Y/N ?", vbYesNo,
    "Exception found!")

    If intResponse = vbYes Then
    ConvExceptions = strFind
    Exit Function
    End If
    End If

    ConvExceptions = StrConv(StringIn, 3)

    End Function
    ======

    Be prepared to respond to the message box if an exception is found.

    Use it in a Control's AfterUpdate event to check when new names are
    added:

    If Not IsNull([ThisField]) Then
    [ThisField] = ConvExceptions([ThisField])
    End If

    Add new names to the exceptions table as they occur.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    for future reference, please use the code tags to preserve indentation and readability of code - highlight the code and click the # button

    but to answer your question you would need to split the string into its separate words

    in vba something like

    Code:
    function properNames(s as string) as string
    dim a() as string
    dim i as integer
    
        'Get the case for each word
        set a=split(s," ")
        for i=0 to ubound(a)-1
            a(i)=ConvExceptions(a(i))
        next i
    
        'stitch the name back together
        for i=0 to ubound(a)-1
            propernames=propernames & " " & a(i)
        next i
    
        'remove initial space
        propernames=mid(propernames,2)
    
    end function

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    bcarter17,

    Can you provide some samples (several) of the names you'll be dealing with?

  11. #11
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    Sorry about the code post issue, Think I got it right this time. So, I see where it puts a call in for ConvExceptions. Tried to call ProperNames as a public function from a module, but got an error "Not Allowed'. To run this, does the search portion of my initial code need to be removed or replaced and simply run the ConvExceptions "True" portion of the If/Then?

    Below is how I have the code set up in Module 1 for a public function

    Code:
    Public Function ConvExceptions(StringIn As String) As String
    
    
    ' Will find exceptions to Proper Case capitalization of names.
    
    
    On Error Resume Next
    
    
    If DCount("*", "tblExceptions", "[ExceptName] = " & Chr(34) & StringIn & Chr(34) & "") > 0 Then
    Dim intResponse As Integer
    Dim strFind As String
    strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] = " & Chr(34) & StringIn & Chr(34) & "")
    intResponse = MsgBox(strFind & vbCrLf & " is an exception name." & vbCrLf & " Accept the above capitalization? Y/N ?", vbYesNo, "Exception found!")
    If intResponse = vbYes Then
    ConvExceptions = strFind
    Exit Function
    End If
    End If
    
    
    ConvExceptions = StrConv(StringIn, 3)
    
    
    End Function
    Quote Originally Posted by Ajax View Post
    for future reference, please use the code tags to preserve indentation and readability of code - highlight the code and click the # button

    but to answer your question you would need to split the string into its separate words

    in vba something like

    Code:
    function properNames(s as string) as string
    dim a() as string
    dim i as integer
    
        'Get the case for each word
        set a=split(s," ")
        for i=0 to ubound(a)-1
            a(i)=ConvExceptions(a(i))
        next i
    
        'stitch the name back together
        for i=0 to ubound(a)-1
            propernames=propernames & " " & a(i)
        next i
    
        'remove initial space
        propernames=mid(propernames,2)
    
    end function

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ajax View Post
    ...you would need to split the string into its separate words

    in vba something like

    Code:
    ...
        'Get the case for each word
        set a=split(s," ")
    ...
    OP, what is the nature of the strings you're converting? Will you need to account for punctuation?

    For example,
    Let's say you had the exception "WORLD" in your table and your functions were given the string "HELLO WORLD!" to process, you would expect "Hello World!" in return but given the code presented so far you would get "Hello WORLD!" instead because "WORLD!" doesn't match "WORLD" in your exception table due to the punctuation.

    Et al, would a reg ex based function make life easier? would something along the lines of regex.pattern = "(\w+)" be more robust?

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your code should be indented like this

    Code:
    Public Function ConvExceptions(StringIn As String) As String
    
    ' Will find exceptions to Proper Case capitalization of names.
    
        On Error Resume Next
    
    If DCount("*", "tblExceptions", "[ExceptName] = " & Chr(34) & StringIn & Chr(34) & "") > 0 Then
        
    Dim intResponse As Integer
    Dim strFind As String
    strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =" & Chr(34) & StringIn & Chr(34) & "")
    intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
    vbCrLf & " Accept the above capitalization? Y/N ?", vbYesNo,"Exception found!")
    
    If intResponse = vbYes Then
    ConvExceptions = strFind
    Exit Function
    End If
    
    End If
    
    ConvExceptions = StrConv(StringIn, 3)
    
    End Function
    note that
    strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =" & Chr(34) & StringIn & Chr(34) & "")

    was showing as

    strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =
    " & Chr(34) & StringIn & Chr(34) & "")

    and that won't run. Which begs the question, what code are you actually running.


    To run this, does the search portion of my initial code need to be removed or replaced and simply run the ConvExceptions "True" portion of the If/Then?
    Both functions need to be in a standard module (not a form module or class module) and you just need to pass the name as a parameter - in the query builder it would look something like


    ProperCompanyName: ProperNames([CompanyName])

  14. #14
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    I am using Access 2007 and VBA......I thought in my actual example I sent after your callout, I showed that I had it in correct in the module?



    Quote Originally Posted by Ajax View Post
    your code should be indented like this

    Code:
    Public Function ConvExceptions(StringIn As String) As String
    
    ' Will find exceptions to Proper Case capitalization of names.
    
        On Error Resume Next
    
    If DCount("*", "tblExceptions", "[ExceptName] = " & Chr(34) & StringIn & Chr(34) & "") > 0 Then
        
    Dim intResponse As Integer
    Dim strFind As String
    strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =" & Chr(34) & StringIn & Chr(34) & "")
    intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
    vbCrLf & " Accept the above capitalization? Y/N ?", vbYesNo,"Exception found!")
    
    If intResponse = vbYes Then
    ConvExceptions = strFind
    Exit Function
    End If
    
    End If
    
    ConvExceptions = StrConv(StringIn, 3)
    
    End Function
    note that
    strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =" & Chr(34) & StringIn & Chr(34) & "")

    was showing as

    strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =
    " & Chr(34) & StringIn & Chr(34) & "")

    and that won't run. Which begs the question, what code are you actually running.




    Both functions need to be in a standard module (not a form module or class module) and you just need to pass the name as a parameter - in the query builder it would look something like


    ProperCompanyName: ProperNames([CompanyName])

  15. #15
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    Our biggest need is that we have entries on load tickets where trucklines are named KDB, ABF, We have special instructions at times where the customer uses a MDA number. Some of our customer names are KCPL, MMGW Construction, etc.

    We can maintain an exceptions table, but just trying to find the best way when users are creating an entry on a form that it doesn't override the exceptions.



    Quote Originally Posted by kd2017 View Post
    OP, what is the nature of the strings you're converting? Will you need to account for punctuation?

    For example,
    Let's say you had the exception "WORLD" in your table and your functions were given the string "HELLO WORLD!" to process, you would expect "Hello World!" in return but given the code presented so far you would get "Hello WORLD!" instead because "WORLD!" doesn't match "WORLD" in your exception table due to the punctuation.

    Et al, would a reg ex based function make life easier? would something along the lines of regex.pattern = "(\w+)" be more robust?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Proper Case
    By jonboy in forum Access
    Replies: 9
    Last Post: 04-05-2021, 01:49 AM
  2. Replies: 5
    Last Post: 05-14-2019, 07:48 AM
  3. Replies: 4
    Last Post: 04-28-2019, 07:19 PM
  4. Change text case from upper to proper case
    By s.nolan in forum Access
    Replies: 1
    Last Post: 12-02-2015, 10:56 AM
  5. Proper Case or Capitalization help
    By tshirttom in forum Programming
    Replies: 5
    Last Post: 03-23-2012, 10:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums