• Change CAPS to Title Case

    Author
    Topic
    #470081

    Hi,

    I have 2000 records where we have 10 fields of capital letter’d text.

    I have been asked if it is possible to change this to ‘Title Case’ where only the first letter of each word has a capital letter.

    Is this possible with automation?

    Viewing 5 reply threads
    Author
    Replies
    • #1232384

      Do you want to permanently changed the stored values, or just display them in title case?

      In either case you can use the StrConv function to convert to Title case. To permanently change them use an Update query. To just display them create a calculated field in a query.

      In either case use the expression: StrConv([fieldname],3)

    • #1232393

      Thanks

    • #1232397

      John – thanks for the reference to StrConv. I can’t tell you how many times (more than once for sure) I’ve written code to parse strings, then capitalize the first letter of each word. Nice find!

    • #1232401

      It interesting that so many have difficulty finding that function – it’s been there since Access 2.0. I went for a number of years not needing to do that and when I did finally need it (to convert a list of about 50,000 names entered as all caps), I had to do a web search to find it again.

    • #1232458

      Hi all,

      You could also change the input mask for the field in question.

      The input mask would be >L<?????????????? This would capitalize the first letter of each word. It would have to be a text field for this to work.

      This solution would fix the problem at the source. All future data entry would be in the proper format which would preclude having to run a function whenever you wanted to see your data in this format. In Access 2007 you can also include an output mask to just output the data in your requested format.

      Access will ask you if you want to change all the related values in the table when you add the mask.

    • #1233577

      If you want to get really fancy – here is a little procedure I wrote (or stole and rewrote to be more accurate) that will hanldle exceptions to just captitalising the first letter of each word.

      It handles names like O’Connor, MacDonald or McDonald but also allows you to enter words in a table for specific exceptions like Mackenzie.

      It can then also be used on lists other than names like Product codes where you may want to retain XXL or USA

      I’ve now uploaded the missing file!

    Viewing 5 reply threads
    Reply To: Change CAPS to Title Case

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: