Using Proper case with exceptions such as "and" "the" "is"

[wes9659]

I have created field named Family. I would like it to be in proper case and be triggered as an after-update event.
Me.Family = StrConv(Me.Family, vbProperCase)
However, I would like the words “and” “the” “is” to remain in lower case. Does anyone know how I would achieve this? Thank You in advance for all your help.

[Micron]

I might be too late in answering, but if those words are in the same field (which seems odd for family last name if that's what this is about) you will need a custom function that can refer to a list of words that you don't want included (or are those 3 the only ones?).

[Missinglinq]

...if those words are in the same field (which seems odd for family last name if that's what this is about)...

I agree! It really sounds as if you're inappropriately storing multiple pieces of data in a single field.

Could we have some examples of this data...or better yet, a copy of your file with the name of the form involved ?

Linq ;0)>

[CJ_London]

proper case has its limitations - what about

McDonald
O'Leary
Saint-Stephens

etc

[rpeare]

I agree with ajax. your better option would be to use UCASE to store it as all upper case. I'll go a step further than Ajax too

MacDonald is valid
Macinaw is valid

How do you determine which one should be capitalized?

Seems to me you would have to have a fairly sophisticated algorithm to figure it all out if your data entry folks didn't type it in correctly where an all caps would take all of that away.

Finally, you do not have to STORE your data the way you're proposing. You can store the data how it was originally entered and convert it whenever you need to display it on reports etc. This way you can go back to see what was originally entered if you encounter a 'weird' one.

[CJ_London]

if you are going to continue with your propercase you could try

Me.Family = replace(replace(replace(StrConv(Me.Family, vbProperCase)," and "," and ")," the ", " the ")," is "," is ")

note the spaces to identify separate words and not to change first or last words