Change CAPS to Title Case

Topic

New Reply

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?

Reply | Quote

Replies

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)

Reply | Quote

Thanks

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote