Results 1 to 4 of 4
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Update Query (in VBA)

    Experts:

    I need some assistance with automatically updating field values into the proper format. Right now, I use the below VBA to change the field values upon closing a form.

    For example, on the "Billets" form, I have the field [Ra_Billet_Title] automatically change to upper case. This works fine as is!
    Also, on the "Staff Members" form, I have the field [All_Personal_Email] automatically change to lower case. This also works fine as is!

    However, I have an address field where each word must be capitalized. So, a data entry of, e.g., "123 MAIN STREET APT 200" needs to change to "123 Main Street Apt 200".

    How do I modify the 2nd update query in the Form "Staff Members" where I'm currently using the UCase? Is there something like "CCase" (CapitalizeCase)?

    Thank you,
    EEH



    Code:
    ********** Form "Billets" **********
    Private Sub Form_Close()
            'Update billet title to UPPER case
            CurrentDb.Execute "UPDATE T01_Billets SET T01_Billets.Ra_Billet_Title = UCase([Ra_Billet_Title]);"
    End Sub
    
    
    ********** Form "Staff Members" **********
    Private Sub Form_Close()
           'Update email to lower case
            CurrentDb.Execute "UPDATE T01_StaffMembers SET T01_StaffMembers.All_Personal_Email = LCase([All_Personal_Email]);"
            
            'Update email capitalize each word
            CurrentDb.Execute "UPDATE T01_StaffMembers SET T01_StaffMembers.All_Address = UCase([All_Address]);"                    'Rather than UCase, I need to capitalize each word
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    StrConv(All_Address, vbProperCase)

    Numeric constant for vbProperCase is 3.

    StrConv(All_Address, 3)

    Means you will get results like:

    123 O'rourke Road

    123 Mcdonnell Street
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Perfect! Thank you June7.

  4. #4
    roaftech is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2012
    Location
    Romania
    Posts
    65
    Just a quick point about Lcase, Mid, etc string functions:
    I was reviving an old (Access 2003) database using these functions in queries and the "undefined function" error came up.
    The solution for me was to ensure that "Microsoft ActiveX Data Objects(Multi-dimensional) 2" was added into the relationships - Alt-F11 > Tools > Relationships. Exit Access and restart; problem apparently resolved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-03-2017, 04:58 AM
  2. Replies: 1
    Last Post: 10-27-2016, 12:14 PM
  3. Query Update/Select Query (Wont Update)
    By NickWren in forum Access
    Replies: 1
    Last Post: 03-30-2016, 10:41 AM
  4. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  5. Replies: 2
    Last Post: 08-30-2012, 07:59 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