Results 1 to 6 of 6
  1. #1
    MJays is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    6

    How to change lowercase to uppercase when it comes before a comma

    Hi all,

    It's the newby team again. There are two parts to this question.

    1.

    For the life of us, we can't work out how to change a word to uppercase when it comes before a comma. (FYI, there is always a comma).



    Eg. Doe, John

    How do you make Doe, John into DOE, John.


    2.

    And if we have a Doe,John (without the space after the comma), how do we make it DOE, John (to include the space after the comma).

    Any help would be most appreciated.

    We have tried Left functions, Iif functions.... all sorts.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1. try the instr function

    myfield=ucase(left(myfield,instr(myfield,",")-1) & mid(myfield,instr(myfield,","))

    2. use the replace function, then remove double spaces

    Code:
    replace(replace(myfield,",",", "),"   "," ")
    combining the two you would have

    Code:
    myfield=ucase(left(myfield,instr(myfield,",")-1) & replace(replace(mid(myfield,instr(myfield,",")),",",", "),"   "," ")

  3. #3
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    to convert Doe, John to DOE, John

    we assume your field is called [name]

    1. find the comma: InStr(1,[name],",",1)

    this returns a number, n, the position in your string where the comma occurs.

    2. take the left n-1 characters: Left([name],InStr(1,[name],",",1)-1)

    3. turn this into Upper case: UCase$(Left([name],InStr(1,[name],",",1)-1))

    4. add the rest of the string on the end:
    Code:
    UCase$(Left([name],InStr(1,[name],",",1)-1)) & ", " & Right([name],Len([name])-InStr(1,[name],",",1))
    it is probably worth noting that Ucase() returns a variant, and Ucase$() returns a string.


    many thanks,


    Cottonshirt
    Last edited by Cottonshirt; 04-07-2020 at 04:29 AM. Reason: included code tags

  4. #4
    MJays is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    6
    Thanks so much, Cottonshirt. They both worked perfectly, and we really appreciated the explanation as you progressed. It actually made sense to some of us. SO much to learn.

    Thanks Ajax. Once again, you came to our aide, but unfortunately we couldn't get them to work for some reason. We just kept getting a Syntax error message.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    ah - missed a bracket off

    myfield=ucase(left(myfield,instr(myfield,",")-1)) & mid(myfield,instr(myfield,","))

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Where is your data coming from? Is it being entered as LastName, Firstname as one field or seperate fields? Or is it coming to you some other way?
    Name fields are often problematic and are usually best stored in the smallest parts (ie. Salutation,FirstName,MiddleName,LastName,Suffix) Doing so solves most problems.

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

Similar Threads

  1. Change comma to point in double
    By jaryszek in forum Access
    Replies: 4
    Last Post: 07-06-2018, 09:01 AM
  2. Replies: 4
    Last Post: 02-23-2015, 03:55 PM
  3. Replies: 1
    Last Post: 03-01-2011, 03:01 PM
  4. Changing table names from lowercase to uppercase
    By supernix in forum Database Design
    Replies: 1
    Last Post: 09-03-2010, 10:08 PM
  5. Uppercase to Lowercase
    By tigers in forum Reports
    Replies: 1
    Last Post: 09-28-2009, 10:48 AM

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