Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 32
  1. #16
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can you show us what your tables look like? Maybe a screenshot of your relationship window? I'm wondering if it might be better that these are foreign key fields instead of text fields that you're trying to format... it would be a lot simpler that way.

  2. #17
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    The form fields are unbound. I use VBA to create a record once the forms data has been validated as complete. I created the exceptions table as the article directed I found the exception code on. I can use Proper case in most instances for the fields for data entry, but needed some words to be left alone.

    Click image for larger version. 

Name:	Exception2.png 
Views:	17 
Size:	28.7 KB 
ID:	46261Click image for larger version. 

Name:	Exception1.png 
Views:	17 
Size:	25.6 KB 
ID:	46262

    Quote Originally Posted by kd2017 View Post
    Can you show us what your tables look like? Maybe a screenshot of your relationship window? I'm wondering if it might be better that these are foreign key fields instead of text fields that you're trying to format... it would be a lot simpler that way.

  3. #18
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I was interested in the other tables, and the relationship view, to get an idea of the big picture of your project.

    Are you familiar with the concept of foreign keys and linked lookup tables?

    In most cases you would have something like a Customers table and the user would select the customer from a drop down list, this helps avoid mistakes and makes validation painless. If you're already creating an Exceptions table I guess I don't understand why you wouldn't just use look up tables. If it's a new Customer the user can still type the name in the combobox and the form can trigger a 'not in list' event and add it to the lookup table.

    (I apologize in advance if you're already familiar with all of this, I don't know your background or where you're coming from, or the project for that matter)

  4. #19
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, now I'm wondering why you would allow free form entry for data. Shouldn't you just be picking these names from a combo list or listbox where they were properly formatted in the first place (when entered)? Or is something else going on?

    Edit -
    Late to the party again.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #20
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    Totally understand. Yes the form utilizes drop down boxes for static selections and that works fine. I can select our customer, but in our business there is a lot of special delivery instruction for a trucker, or customer instructions like (Deliver to the old KCPL location in Sibley MO. Make sure to let KDB drivers know exactly where to drop the rock at.

    I use automation as much as possible. I started with Access 2003 and used it at Kohl's Department Stores for many years. Now using Access 2007 so still getting a handle on how it differs from what I had been using.

    Click image for larger version. 

Name:	Form.JPG 
Views:	18 
Size:	87.4 KB 
ID:	46263

    Quote Originally Posted by kd2017 View Post
    I was interested in the other tables, and the relationship view, to get an idea of the big picture of your project.

    Are you familiar with the concept of foreign keys and linked lookup tables?

    In most cases you would have something like a Customers table and the user would select the customer from a drop down list, this helps avoid mistakes and makes validation painless. If you're already creating an Exceptions table I guess I don't understand why you wouldn't just use look up tables. If it's a new Customer the user can still type the name in the combobox and the form can trigger a 'not in list' event and add it to the lookup table.

    (I apologize in advance if you're already familiar with all of this, I don't know your background or where you're coming from, or the project for that matter)

  6. #21
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Here is my overkill and probably inefficient function that might do what you want? I've also attached in it in an example db file but I don't know of it'll work in access2007

    Code:
    'You need to add a reference to M$ VBScript Reg Expressions  for this code to work:
    '   Tools > References > Microsoft VBScript Regular Expressions 5.5
    Public Function ConvertToProper(inputText As String) As String
    On Error GoTo ErrHandler
        Dim outputText As String
        Dim regex As RegExp: Set regex = New RegExp
        Dim matches As Object
        Dim match As Variant
        Dim caseException As Variant
        
        outputText = inputText
        
        regex.Global = True ' find all occurances of the pattern
        regex.Pattern = "(\w+)" ' capture all consecutive alphanumeric characters (including underscores)
        
        Set matches = regex.Execute(inputText)
        For Each match In matches ' loop through each word
            
            ' check to see if the word exists in the lookup table
            caseException = DLookup("CaseException", "tblCaseExceptions", "CaseException=""" & match & """")
            
            If IsNull(caseException) Then
                ' if no exception was found then capitalize (all occurrences of) this word.
                outputText = Replace(outputText, match, StrConv(match, vbProperCase))
            Else
                ' if a preformatted word was found then use that instead
                outputText = Replace(outputText, match, caseException)
            End If
            
        Next
        
    ExitHandler:
        'cleanup
        Set regex = Nothing
        Set matches = Nothing
        ConvertToProper = outputText
        Exit Function
        
    ErrHandler:
        MsgBox Err.Description, , "Error #" & Err.Number
        Resume ExitHandler
    End Function
    Attached Files Attached Files

  7. #22
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    I'll take a look at this, thanks for sharing!!


    Quote Originally Posted by kd2017 View Post
    Here is my overkill and probably inefficient function that might do what you want? I've also attached in it in an example db file but I don't know of it'll work in access2007

    Code:
    'You need to add a reference to M$ VBScript Reg Expressions  for this code to work:
    '   Tools > References > Microsoft VBScript Regular Expressions 5.5
    Public Function ConvertToProper(inputText As String) As String
    On Error GoTo ErrHandler
        Dim outputText As String
        Dim regex As RegExp: Set regex = New RegExp
        Dim matches As Object
        Dim match As Variant
        Dim caseException As Variant
        
        outputText = inputText
        
        regex.Global = True ' find all occurances of the pattern
        regex.Pattern = "(\w+)" ' capture all consecutive alphanumeric characters (including underscores)
        
        Set matches = regex.Execute(inputText)
        For Each match In matches ' loop through each word
            
            ' check to see if the word exists in the lookup table
            caseException = DLookup("CaseException", "tblCaseExceptions", "CaseException=""" & match & """")
            
            If IsNull(caseException) Then
                ' if no exception was found then capitalize (all occurrences of) this word.
                outputText = Replace(outputText, match, StrConv(match, vbProperCase))
            Else
                ' if a preformatted word was found then use that instead
                outputText = Replace(outputText, match, caseException)
            End If
            
        Next
        
    ExitHandler:
        'cleanup
        Set regex = Nothing
        Set matches = Nothing
        ConvertToProper = outputText
        Exit Function
        
    ErrHandler:
        MsgBox Err.Description, , "Error #" & Err.Number
        Resume ExitHandler
    End Function

  8. #23
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Based on similar posts you may consider adding tests for some of the following:
    McDonald's, MacDonald, MacDonald's, sam and ella's restaurant
    , street coordinates eg S,SW,SE,N,NE,NW, states and/or provinces
    ,Ross & Bessie C Thompson


    "John Doe" 'valid data
    " Jane smith " 'leading trailing space
    "GeneRal PurPose" ' multiple intermediate spaces
    "Oscar de la Renta" 'non standard format

    More info on capitalization etc.

    Here's an older thread that may offer some insight.
    Last edited by orange; 09-22-2021 at 04:41 PM. Reason: add'l info

  9. #24
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    So, kd2017, I like how this works, but need some guidance on how I could add it to my existing form. The field on the form will not just be a single word. They could contain 1 or more sentences, like "KDB drivers, please enter through the KCPL gate when you arrive." Right now, we have proper case set for these fields and don't mind that they capitalize every word. But we need certain words like company names (KCPL, KDB, etc.) to remain in all caps. So, can the module you built test all the words in a unbound text box against the exception list, and can it be called as an "AfterUpdate" event for the field on the form?

    Seems like it works fine with your test example you sent in Access 2007.

    Quote Originally Posted by bcarter17 View Post
    I'll take a look at this, thanks for sharing!!

  10. #25
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    don't mind that they capitalize every word
    .

    Do you need ProperCase? The simple solution may be to put everything in Upper case.
    Very easy to implement, and I suggest the "Before Update" event. (the last chance before record is written/accepted)

  11. #26
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    So, can the module you built test all the words in a unbound text box against the exception list, and can it be called as an "AfterUpdate" event for the field on the form?
    Yes. The code uses what is called a regular expression to breakdown a given string into groups of consecutive letters and numbers, we're just assuming these are words. Then for each group of characters, or each word, it checks the word against the exceptions table, if it finds the word in the table it will replace each occurance of the word within the string with the preformatted text from the table. If it doesn't find the word in the exceptions table it will replace each occurrence with the capitalized version of the word.

    Quote Originally Posted by bcarter17 View Post
    So, kd2017, I like how this works, but need some guidance on how I could add it to my existing form.
    So in your forms design view click your text box and create an after update event. The code in this event would simply look something like
    Code:
    Me!FIELD_NAME_HERE = ConvertToProper(Me.TEXTBOX_NAME_HERE)
    *NOTE: You will need to modify the call to DLookup in my code to match your database's table and field names. Based on the screenshot you provided in post #17 the line in my function that looks like this:
    Code:
    caseException = DLookup("CaseException", "tblCaseExceptions", "CaseException=""" & match & """")
    Would become:
    Code:
    caseException = DLookup("ExceptName", "tblExceptions", "ExceptName=""" & match & """")

  12. #27
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    So, I hate to keep throwing monkey wrenches into the solution, but you show that the AfterUpdate event
    Code:
    Me!FIELD_NAME_HERE = ConvertToProper(Me.TEXTBOX_NAME_HERE)
    with a textbox and a field name. My form is not bound to a table, it updates the table thru VBA
    Code:
    rstUpdate.AddNew  rstUpdate!tablefield = formfield   rstUpdate.Update
    So does that have an impact on your code suggestion?



    Quote Originally Posted by kd2017 View Post
    Yes. The code uses what is called a regular expression to breakdown a given string into groups of consecutive letters and numbers, we're just assuming these are words. Then for each group of characters, or each word, it checks the word against the exceptions table, if it finds the word in the table it will replace each occurance of the word within the string with the preformatted text from the table. If it doesn't find the word in the exceptions table it will replace each occurrence with the capitalized version of the word.

    So in your forms design view click your text box and create an after update event. The code in this event would simply look something like
    Code:
    Me!FIELD_NAME_HERE = ConvertToProper(Me.TEXTBOX_NAME_HERE)
    *NOTE: You will need to modify the call to DLookup in my code to match your database's table and field names. Based on the screenshot you provided in post #17 the line in my function that looks like this:
    Code:
    caseException = DLookup("CaseException", "tblCaseExceptions", "CaseException=""" & match & """")
    Would become:
    Code:
    caseException = DLookup("ExceptName", "tblExceptions", "ExceptName=""" & match & """")

  13. #28
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You would change rstUpdate!tablefield = formfield to rstUpdate!tablefield = ConvertToProper(formfield)

  14. #29
    bcarter17 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    35
    Thanks so much for the suggestion. I will get started updating the form this afternoon in test mode and give it a try.


    Quote Originally Posted by kd2017 View Post
    You would change rstUpdate!tablefield = formfield to rstUpdate!tablefield = ConvertToProper(formfield)

  15. #30
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi all!

    I think the opposite way is safer and more efficient.
    That is, if the exceptions have removed initially from the input text, we can convert safely only the remaining words. The exceptions can be complex frases instead of single words and, by this way, we have only one query to the table of exceptions instead of multiple lookups.

    Using the table of attached file of kd2017, I made the bellow procedure based on the above plan.

    Code:
    Function ProperAll(ByVal strIn As String, Optional ByVal SkipExceptions As Boolean = True) As String
        'Returns the strIn with all words in proper case
        'exept those that are in table tblCaseExceptions
        'if SkipExceptions is TRUE.
        Dim rsExp As dao.Recordset
        Dim i As Integer
        Dim intPos As Integer
        Dim strExp As String
        Dim strTemp As String
        Dim strW As String
        Dim varW As Variant
        
        On Error GoTo ErrHandler
        strTemp = strIn
        If SkipExceptions Then
            'Get the exceptions.
            Set rsExp = CurrentDb.OpenRecordset("SELECT CaseException FROM tblCaseExceptions", dbOpenForwardOnly)
            While Not rsExp.EOF
                strExp = rsExp(0)
                'Search for exception in temporary text.
                If InStr(1, strTemp, strExp, vbBinaryCompare) > 0 Then
                    'Remove the exception from temp text.
                    strTemp = Replace(strTemp, strExp, " ")
                End If
                rsExp.MoveNext
            Wend
        End If
        'Remove extra spaces.
        While InStr(1, strTemp, "  ") > 0
            strTemp = Replace(strTemp, "  ", " ")
        Wend
        'Get the remaining words.
        varW = Split(Trim(strTemp))
        'Convert the input text using only the remaining words.
        For i = LBound(varW) To UBound(varW)
            strW = varW(i)
            'Find the current word in text.
            intPos = InStr(intPos + 1, strIn, strW)
            'Convert it to proper case.
            Mid(strIn, intPos, Len(strW)) = StrConv(strW, vbProperCase)
            'Set the position in text to the end of this word.
            intPos = intPos + Len(strW)
        Next i
    ExitHere:
        'Return.
        ProperAll = strIn
        On Error Resume Next
        rsExp.Close
        Set rsExp = Nothing
        On Error GoTo 0
        Exit Function
    ErrHandler:
        Resume ExitHere
    End Function
    @bcarter17
    Adapt the names of the field and the table in OpenRecordset expression to the actual names of your objects, as kd2017 noted.

    By setting the second optional argument to False, the procedure ignores the exceptions.

    You can use the functions that provided when you need to display the text in proper case (in forms and reports) keeping the original text in table as it typed. Try a query like this:
    Code:
    SELECT ProperAll(nz([Your_Field])) AS Converted FROM [Your_Table];
    Or, you can use it as ControlSource in a unbound textbox in a form/report like this:
    Code:
    =ProperAll(nz([Your_Field]))
    Cheers,
    John

Page 2 of 3 FirstFirst 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