Name Formatting and changing case. 2

[kentwoodjean]

I have a table that has several thousand records including that of a customer name. We are going to have to use mail merge to send out some monthly letters and I need a bit of formatting help on the name. The name field appears with last name first, space, and first name all in Caps (I.e. DOE JOHN). Here are 2 things that I need to do and am looking for the quickest and easiest way to do it:

(1) Need to conver the Name to lower case yet have the first letter of each part of the name in Caps. I currently have the expression - Expr1: LCase([SUBS_FULL_NAME]) that puts all of it in lower case (2) I need to get a comma between the 2 parts of the name.

Any help would be appreciated.

 

[Golom]

Assuming that ALL your names are structured as

LastName Space FirstName

 Expr1: StrConv( 
    Left([SUBS_FULL_NAME],Instr([SUBS_FULL_NAME]," ")-1 )
   & "," &  
    Mid([SUBS_FULL_NAME],Instr([SUBS_FULL_NAME]," ")+1 ), 
        3)

 

[kentwoodjean]

Perfect. If I can ask, based on your expression, what is it that is triggering the Cap of the 1st letter? Reason I ask is that I need to put the address in lower case as well, obviously keeping the 1st letter in caps. I tried twisting the expression a bit to fit the address piece but it is not working. Since there are also numbers prior to the street name, I assume it is different. Once that is done, I will finally be converting just the 1st name of the Spouse in the same manner.

 

[PHV]

StrConv([some text field],3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

The StrConv function is documented here. Specifically

StrConv ("SOME STRING", 3)

produces "Some String" and "3" specifies the type of conversion (called "Proper Case") that capitalizes the first letter of each word and makes all other letters lower case.

The presence of numbers doesn't matter since they don't have upper or lower case versions (i.e. they will be unchanged.)

Note that StrConv works sorta OK for names but some things like O'Hara or McKay will end up as O'hara and Mckay. If you want to handle those things you may need to write your own function (i.e. a UDF) to deal with the special cases.

 

[kentwoodjean]

Interesting but I was into the Microsoft site but was looking at lower and upper case rather than StrConv. makes a big difference if you know what you are looking for. Thanks for the explanation and pointing me in the right direction.

I was courious as your use of the word "Mid" in the expression. I would have thought that would apply to a Middle name or initial. With just the 2 words for the name I would have assumed that you would use right and left. If for example, you were going to go with 1st name last name using the same capitalization function, would then then reverse the order of Mid and Left and remove the comma?

Sorry for all the questions, but I may as well get an understanding on this once and for all so I can esily apply it as data arrives.

 

[Golom]

The Mid function has two forms

Mid([SomeString], StartPosition, CharCount)
Returns "CharCount" characters from "SomeString" beginning with the character in "StartPosition".

Mid([SomeString], StartPosition)
Returns the characters in "SomeString" beginning at "StartPosition" to the end of the string.

You could use "Right" but that would require that you did a computation of exactly how many characters you needed to return. With "Mid" you only need to know where to start.

 

[kentwoodjean]

Got it. thanks again for the really good explanation. Needless to say, this will be filed for future reference.