Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    9

    Post Unanswered: Format Text Fields

    I have data that is captured by a third party from a web page - name, address, etc. They send it to us on a nightly basis. I use this info to create mailing labels for individuals who do not use an email address. The issue is the fact that the user may enter their info in all caps, mixed case, etc. In order to create nice looking labels, I would like to format the name in proper case. Is there a format command of some sort I can use when I do the query or when I create the report?

    Thanks - Terrie

  2. #2
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109

    how to format string lcase ucase

    Here an SQL query using Lcase and Ucase:
    ptext is the TextFile
    tblbtext is the table

    SELECT LCase([ptext]) AS Expr1,
    UCase([ptext]) AS Expr2,
    UCase(Left([ptext],1)) & LCase(Right([ptext],Len([ptext])-1))
    FROM tbltext;


    The first part select the text in LowerCase, the second select the test in UpperCase, and the third will select the text in LowerCase except the first character that will be in UpperCase. I'm pretty sure there is an SQL function for that third part, but I can't remember, so until someone post it, that will do the job.

    JefB - hope it helps

  3. #3
    Join Date
    Aug 2002
    Posts
    9

    Smile Formatting Text

    This works great! Thanks! One more question...How can I do a city that has a space in the name (ex. Ft. Wayne, Las Vegas)?

    Thanks AGAIN!

    Terrie

  4. #4
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228

  5. #5
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109

    name with space

    You'll have to create a function that use the following code:

    Code:
        'Someone has really badly written that
        Dim str As String
        str = "ft. WaYNE, LAS VEGAs"
        
        'Let's show that oddity
        MsgBox str
        
        'Split all word from the string
        Dim arr() As String
        arr = Split(str)
        
        'Change all word
        For x = 0 To UBound(arr)
            arr(x) = UCase$(Left$(arr(x), 1)) & LCase$(Mid$(arr(x), 2))
        Next x
        
        'Unsplit the words
        str = Join$(arr, " ")
        
        'Show the well formatted string
        MsgBox str
    JefB - hope it helps

Posting Permissions

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