Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    29

    Unanswered: Switching Caps names to lower case.

    for example.... JOHN | VOIGHT

    i want that to be John | Voight

    i need to do this to the name and last name columns
    i have 80+ columns and 200,000+ rows


    thanks for all the help in advance

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this is a lift from m$kb ...won't be fast on your 200000 rows though!

    Public Function Pcase(myString as string) as string
    Dim Temp as string
    dim Char as string
    dim OldChar as string
    dim myInt As Integer
    If IsNull(myString) Then
    Exit Function
    Else
    Temp = CStr(LCase(myString))
    OldChar = " "
    For myInt = 1 To Len(Temp)
    Char = Mid$(Temp,myInt, 1)
    If ((Char >= "a" And Char <= "z") And (OldChar < "a" Or OldChar > "z")) Then
    Mid$(Temp, myInt, 1) = UCase$(Char)
    End If
    OldChar = Char
    Next
    Pcase = Temp
    End If
    End Function


    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2004
    Posts
    29
    so... what do i do with all that?

    sorry but its a little confusing for me

    do i do this in the build section, or what?

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Lowercase is easy and would look like john voight, but what you are asking for is mixed case, John Voight. And it can get pretty tedious to get it right. There are quite a few cases that you need to consider:

    MacDonald
    McDonald
    van Pelt
    O'Donnell
    IBM (Company names with only initials)
    III (The third)

    There are some others that I can remember. You can simply change the first letter to a capital but the cases above won't exactly be right. I work with direct mail and need these to be as accurate as possible so I have bought some software that does this for me. It takes into consideration company names, foreign names etc.

    If you are doing a one time conversion or you won't be doing this everyday then you can put the above code into a new module, then in a query put :

    CorrectedFirstName:PCase(FirstName)
    CorrectedLastName:PCase(LastName)

    In parentheses put the name of the field that holds the first and last name, that will be passed to the PCase function and return a somewhat corrected uppercase name.

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by idioteque
    for example.... JOHN | VOIGHT

    i want that to be John | Voight

    i need to do this to the name and last name columns
    i have 80+ columns and 200,000+ rows


    thanks for all the help in advance
    Hi idioteque.....
    To make things simple for you, go to your Form. Go to the Field you desire such as FirstName , LastName or whatever.
    Go to the Form/DesignView and RightClick on the desired field...example LastName. RightClick on that Field and then click on Properties. Go to the DataTab and look for Input Mask and type in this in that box: >L<?????????? and that should give you what you want. Now when you type in that field, the First character will be uppercase and the rest will be lowercase.
    Hope that gets you taken care of. Simple and sweet,

    have a nice one,
    Bud

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what do i do with all that?

    stick it in a module just like it is.

    make yourself a query that pulls firstname, lastname
    when it works, add a couple more fields to the query:

    pcaseLast: = pcase(lastname)
    pcaseFirst: = pcase(firstname)

    if you prefer SQL:
    SELECT pcase(lastname) AS pcaselast, pcase(firstname) AS pcasefirst FROM yourTable;

    if you can make that fly, switch it into an update query.

    it's not a particularly smart algorithm - it caps any character following a non-alpha (a...z, A...Z) plus the first character of the input string.

    EAT aT joeS -> Eat At Joes
    Tony McDonald -> Tony Mcdonald (unfortunately!)

    Note: the function MUST be public AND in scope when the query runs (in the code module of an open form or in a "global" module")


    izy
    Last edited by izyrider; 10-30-04 at 10:00.
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and if you are fussy about McDonald you could run through the rows again looking for <space>Mc and caps the next char.

    MacDonald would be more difficult...
    Tony Macedo -> Tony MacEdo is the problem.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Oct 2004
    Posts
    29
    thanks a lot i got it now

Posting Permissions

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