Results 1 to 5 of 5
  1. #1
    wes9659 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Location
    Dover, Ohio
    Posts
    88

    Validation Rule to Capitalize Only First Letter

    I know that the following Validation Rule would require all Entered Last Names to be in Uppercase letters (i.e. REAGAN) StrComp(UCase([LastName]),[LastName],0) = 0



    But how would Validation Rule look to only capitalize the first letter only? (i..e. Reagan)

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    StrConv("REAGAN", vbProperCase)

  3. #3
    wes9659 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Location
    Dover, Ohio
    Posts
    88
    I tried the code as a validation rule and it did not work received errors. What would be the proper way from the main data table to force it to convert to proper text even if I did not force a validation rule? Even tired wild card. How would I just get Access 2010 to change input atomically? Or is there another way to force compliance of using proper text. Novice here baby steps please

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    All I did was show an example of the StrConv() function. I use it in VBA. It may work in a query too, I do not know.

    I am not sure what a validation rule in a table is. Aside from limiting the number of characters in a text field I do not do much at the table level. I manage constraints with forms and VBA.

    If a user types into a bound textbox control on a form, you can use an after update event to convert the text inside the text control and this will be displayed in the relative field within the table. You can apply VBA to unbound controls and forms too. I am just explaining how to directly affect change to a field in a table.

    You can include VBA behind a form via the VBA editor. To create a new sub procedure for a click event....

    While in Design View of the form,

    click to highlight the textbox control
    Go to the Property Sheet and locate the On After Update event within the "Event" tab
    Click the ellipses (...) next to the desired event
    Select the "Code Builder" option

    The cursor will be placed in the middle of your new After Update event
    Paste the following as a placeholder for you future code
    'Type code below this line and before the End Sub line

    Here is your After Update VBA code for a textbox named, "txtFirstName"

    Code:
    Me.txtFirstName.Value = StrConv(Me.txtFirstName.Value, vbProperCase)
    As for the validation thing at the table layer.... I am not familiar with the strComp() function but you can replace the UCase() function with the StrConv() function so I would interpret and change your original code to
    (i.e. REAGAN) StrComp(StrConv([LastName],3),[LastName],0) = 0

    Notice I changed the vbpropercase to 3

    This may be needed if using the function somewhere constants are not recognized for the integer they represent.

    I believe the following is correct
    vbUpperCase = 1
    vbLowerCase = 2
    vbProperCase = 3

  5. #5
    wes9659 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Location
    Dover, Ohio
    Posts
    88
    I went to Design View and selected property sheet. Under Event Table Tab I chose After Update than hit ....... to right chose Code Builder and entered

    Private Sub LastName_AfterUpdate()
    Me.LastName = StrConv(Me.LastName, vbProperCase)
    End Sub

    Now when someone enters all lower case or all upper case it automatically converts it over to Proper case in corresponding table as well as associated form. Thanks all for the help

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

Similar Threads

  1. Validation Rule
    By Sanjo in forum Forms
    Replies: 2
    Last Post: 12-10-2013, 11:02 PM
  2. Validation Rule
    By noob123 in forum Access
    Replies: 1
    Last Post: 12-02-2013, 03:39 PM
  3. IIf in Validation Rule
    By Bugsy in forum Access
    Replies: 6
    Last Post: 11-21-2011, 11:33 AM
  4. Validation Rule
    By robomose in forum Access
    Replies: 1
    Last Post: 09-09-2009, 05:59 AM
  5. Validation Rule
    By smitstev in forum Access
    Replies: 5
    Last Post: 06-30-2009, 09:58 AM

Tags for this Thread

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 - Senior Forums