Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    3

    Question Unanswered: Change Case to Title Case?

    I copied someone's code from this forum for change a field that was in all capitals to first letter upper, rest of field lower. Now I cannot find that person's name or email. My new problem to correct: code for changing an address field to what would be Title Case in Word....first letter of each word in the address field should be capitalized. Although that does not solve the problem of addresses such as NE 1st Place, that would then come out Ne 1st Place. Hmmm, anyone have any suggestions? Maybe I should change this field to all caps? How do I do that? Surely there are other who have had this problem in their databases? Thanks for any help and your time!

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    To change a string in all uppercase use the ucase() function.
    Here's a function to change every first letter of a word to upper case

    Code:
    Function fFirstUpper(ByVal vstrIn As String) As String
    
        Const cSpace As String = " "
        
        Dim strOut As String
        Dim strCur As String
        Dim lngPos As Long
        Dim blnNewWord As Boolean
        
        blnNewWord = True
        strOut = ""
        For lngPos = 1 To Len(vstrIn)
            
            strCur = Mid(vstrIn, lngPos, 1)
            
            If strCur = cSpace Then
                strOut = strOut & strCur
                blnNewWord = True
            Else
                If blnNewWord Then
                    strOut = strOut & UCase(strCur)
                    blnNewWord = False
                Else
                    strOut = strOut & LCase(strCur)
                    'strOut = strOut & strCur
                    'if you want to keep case of characters within a word
                End If
            End If
        
        Next lngPos
        fFirstUpper = strOut
        
    End Function
    Are your fields all lowercase or mixed case currently?

  3. #3
    Join Date
    May 2002
    Posts
    3
    Apel, thank you for taking time to write out this code to solve my problem. Right now the contents of this field are lower case except for first letter in field but in most cases that first character is a number because this is an address field. I thought I'd be able to figure out how to make this code run and change everything in the field but I have 0% experience in doing Visual Basic work. So as yet I have not been able to make it work. The other procedure in which I was able to change my field contents used an update to expression in an update query. What I have is 26,000 records in which the address fields need to be updated with proper case for each one. Confusing the issue is that nearly all of these address fields have numbers and letters. Some have directional abbreviations such as NE for North East so if I use a code (if I can figure out how to make it work) it would make it look like this: Ne which wouldn't be correct either. Should I just forget this whole thing and figure out an update query to change everything to all upper case? I just don't know if it's possible to do what I want or not. example:
    4545 nw 104th place apt 244 should be changed to
    4545 NW 104th Place Apt 244 or either
    4545 NW 104TH PLACE APT 244
    Thanks again for your time.....you and the other people helping out us inexperienced Access users are ANGELS.

  4. #4
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Changing all to uppercase is possible, detection for abbreviations and stuff will probably be quite a lot of code (maybe work with a table of known abbreviations). But here's the SQL for the uppercase query:

    UPDATE MyTable SET MyAdress = UCase(MyAddress);

    that's all.

  5. #5
    Join Date
    May 2002
    Posts
    3

    Talking

    Thanks again! I have changed the field to all uppercase now. No problem with that function. I do agree -- I think it's so many exceptions and abbreviations that for now I'm just going to leave them all uppercase.

Posting Permissions

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