Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Best Regards,

    Luke

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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)
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks
    Best Regards,

    Luke

  4. #4
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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!

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    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.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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![attachment=89847:TCase.zip]
    Attached Files Attached Files

Posting Permissions

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