Converting First part of string to Proper

[bearcub]

I have a situation where I have a string with two words in it that are all upper case that I'm running in a query. I want to convert the first word to Proper Case but leave the Acronym as all caps.

For example, I have the Term "ALCOSTA SCC" but it should be "Alcosta SCC". I also have other terms like this that we're using but they vary in length so I need to use a dynamic function.

So, I created a nested formula trying to do this but I'm not really familiar with how Access would so this.

This is what I have:

Service_Center: StrConv(Left([scc],InStr(1,[SCC],"")),3)

I'm using the StrConv function to convert it to proper. However, I need to perform this operation on this term so I'm using the left function. The left function is locating the first "" to extract the first word. The query returns nothing when I run it which tells me that there is something wrong with my formula. The StrConv works because I tested it but the result is "Alcosta Scc" which isn't what I want.

Can you let me know what is wrong with my formula?

Thank you for your help in advance,

Michael

 

[welshgasman]

You do not appear to have a space between your double quotes?
You should be searching for the space to be able to locate the first word.?

 

[bearcub]

Ah, your right. There should be a space, thank you