How can I create an update query that will create mixed case?

[Michelle W.]

I have course titles as a column in a table that need to be mixed case. Some
of the title are all caps and some are all lower case and they need to be
changed to mixed case. However, I don't want the words "the", "a, "an", "of",
"to" etc. changed to upper case unless they are first word of the title. Is
there any way to do this programmatically?

 

[KARL DEWEY]

I can think of a way but it is not programmatically.

Backup the database first.
Use a select query to pull up the primary key and title.
Copy and in MS Word Paste Special - Unformated Unicode Text.
Select all.
On the menu select Format - Change Case - Title case. This makes all first
letter an upper case letter.
Copy and paste append in a temp table.
Build a list of article words in another table with two fields, First letter
caps & first letter lower - "the", "a, "an", "of", "to" etc you want to be
lower case.
Create an update query with this as Update To --
Replace([YourTempTableTitleField],[ArticleListTableUpperField],[ArticleListTableUpperField])

 

[KARL DEWEY]

In my haste I did not edit correctly. Last line should be --
Replace([YourTempTableTitleField],[ArticleListTableUpperField],[ArticleListTableLOWERField])

I also left off the next steps that change any lower case first words back
to upper case.

If the first word in the title is used again it will also be changed.
Example --
The Boy Caught The Snail by The Tail

You could add another field temporarily to your table and update it with the
letter from the first word so that afterwards you can find those you just
change to see if the word is used again in the title.

Modify the update query by adding another field like --
Expr1: Asc(Left([YourTableTitleField],1))
Use a criteria of Between 97 and 122 on the field to pull all records that
have a lower case first letter.

Change the Update To to this --
Replace([YourTempTableTitleField],[ArticleListTableLowerField],[ArticleListTableUpperField])

I can think of a way but it is not programmatically.

Backup the database first.
Use a select query to pull up the primary key and title.
Copy and in MS Word Paste Special - Unformated Unicode Text.
Select all.
On the menu select Format - Change Case - Title case. This makes all first
letter an upper case letter.
Copy and paste append in a temp table.
Build a list of article words in another table with two fields, First letter
caps & first letter lower - "the", "a, "an", "of", "to" etc you want to be
lower case.
Create an update query with this as Update To --
Replace([YourTempTableTitleField],[ArticleListTableUpperField],[ArticleListTableUpperField])

I have course titles as a column in a table that need to be mixed case. Some
of the title are all caps and some are all lower case and they need to be
changed to mixed case. However, I don't want the words "the", "a, "an", "of",
"to" etc. changed to upper case unless they are first word of the title. Is
there any way to do this programmatically?