Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make Text Proper Case (Access 2000)

    If someone types in all uppercase, is there a way to change it all to lowercase, except for the first letter, after it was typed in a field? I know you can go all uppercase or lowercase, but how about having the first letter of each word uppercase, then the rest lowercase

    Thanks

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Make Text Proper Case (Access 2000)

    Try the StrConv function - if you set the conversion type parameter to 3, it changes text to Proper Case.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Text Proper Case (Access 2000)

    Sweet !
    Thanks

  4. #4
    New Lounger
    Join Date
    Apr 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Text Proper Case (Access 2000)

    I found a great example for the event 'On Key Press' up on this forum some time ago and it is great. I use it in almost all my applications that have name and address input.
    I put a function in the module and pass the text and the ascii code from the field to the function.
    My code example:

    Private Sub Address_KeyPress(keyASCII As Integer)
    'capitalize first letter
    strString = Me![Address].Text
    Call CapFirst(strString, keyASCII)
    End Sub

    Public Function CapFirst(mString As String, keyASCII As Integer)
    'cap first letter of each word in name and address
    lngPos = Len(strString)
    If lngPos = 0 Then
    keyASCII = Asc(UCase(Chr$(keyASCII)))
    ElseIf Mid$(strString, lngPos, 1) = Space$(1) Then
    keyASCII = Asc(UCase(Chr$(keyASCII)))
    End If
    End Function

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Location
    New York, New York, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Text Proper Case (Access 2000)

    I tried your Sub and function and got a compile error "Variable Not Defined". I just cut and pasted your code (changing the control name). What could I have done wrong. thanks

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Make Text Proper Case (Access 2000)

    What your code does here is effectively what the StrConv function does.

    I always go with the least amount of code as I can, that much less to debug. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Make Text Proper Case (Access 2000)

    What line of code was highlighted?

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make Text Proper Case (Access 2000)

    That code predates both the StrConv function and the Change event, I believe. It used to be the only way to capture keystrokes as they were entered. It isn't necessary any more because it can be done more simply. The AfterUpdate event of the control can be used to run StrConv and will be less resource-intensive than KeyPress, which fires every time a key is depressed.
    Charlotte

  9. #9
    New Lounger
    Join Date
    Jun 2003
    Location
    New York, New York, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Text Proper Case (Access 2000)

    strString =
    of the line
    strString = Me![FirstName].Text
    is highlighted.

    I haven't worked in Access in a while, so I'm more than a little rusty. I do seem to remember a "Proper" function that was simple and may have been put in the Format field of a text box.

    Thanks, for your help.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make Text Proper Case (Access 2000)

    If you have Option Explicit set in your module (as we strongly recommend you do), then you need to declare the variable either at the module level or in the event procedure before you can use it.

    There is no built-in ProperCase funtion, but there is a vbProperCase constant that can be used to code to pass the value 3 to the StrConv function. Unfortunately, you can't use constants in queries. When you're working there, you have to use the value represented by the constant.
    Charlotte

  11. #11
    New Lounger
    Join Date
    Jun 2003
    Location
    New York, New York, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Text Proper Case (Access 2000)

    Thanks for your reply, but most of that is gibberish to me. Isn't "strString = Me![FirstName].Text " the variable? I'm sorry, but I just don't understand why something so basic (initial caps) is so complicated; just another example of Microsoft being able to very complex things, but not simple things (rant over).

    If I wanted to use the strConv Function, I don't understand what to do after I type "Public Function StrConv(string, conversion, LCID). I tried replacing the text in parens with the Control name and 3, but that didn't work, so I'm obviously missing something; Microsoft help just doesn't give any good examples of what to do with these things.

    Thanks again.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make Text Proper Case (Access 2000)

    No, strString is the variable and it has to be declared like this:

    Dim strString as String

    StrConv is built into VBA, it isn't a function you create. Look it up in the object browser in the VB editor by opening any of the modules in the database and clicking on the object browser, then entering StrConv in the Find combo. Ignore the last argument as long as you aren't trying to use it somewhere besides the current database. If the control name is MyControl, the syntax is StrConv([MyControl], vbProperCase), at least if you're using it in a form. If you're using it in a query, you have to use a 3 instead of vbProperCase because SQL (the query language) doesn't understand VB constants or variables.

    Itnitial caps seems simple, but in fact it isn't that simple at all. Even word processors can have some trouble with it, and Access is a database, not a word processor.
    Charlotte

  13. #13
    2 Star Lounger
    Join Date
    Jul 2003
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make Text Proper Case (Access 2000)

    I have use this code in an update query to change from Uppercase to Lowercase; but now I can't find it. How would use this code.

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

    Re: Make Text Proper Case (Access 2000)

    To change a field MyField to UPPER CASE, you can set the 'Update to' line in the update query to

    UCase([MyField])

    or

    StrConv([MyField],1)

    To change to lower case, use

    LCase([MyField])

    or

    StrConv([MyField],2)

    To change to Proper Case, use

    StrConv([MyField],3)

Posting Permissions

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