Crystal Reports 8 - Using TitleCase

I'm trying to create a field similar to Access Substring function that will place capitalization on the first letter of each word.  Currently the data is in all caps in the database I am retrieving from.  Any suggestions?
lbeesonAsked:
Who is Participating?
 
GJParkerConnect With a Mentor Commented:
aargh

just add e3xtra brackets at the end of each line

Left({Employee.FirstName}, 1) & LCase(Right({Employee.FirstName}, (Len({Employee.FirstName}) - 1)))
& ' ' &
Left({Employee.LastName}, 1) & LCase(Right({Employee.LastName}, (Len({Employee.FirstName}) - 1)))

try this for address

Local StringVar Array Words;
Local NumberVar i;

Words := Split('123 ABC ST,', ' ');

For i := 1 to Ubound(Words)
Do(
Words[i] := UCase(Left(Words[i], 1)) & LCase(Right(Words[i], (Len(Words[i]) - 1))));

Join(Words, ' ') 

Open in new window

0
 
mlmccCommented:
Other than the first word is there a space before each word?

mlmcc
0
 
James0628Commented:
I haven't used CR 8, but CR 10 has a ProperCase function that does that kind of thing.  See if CR 8 has that function.

 James
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
GJParkerCommented:
James is right use the ProperCase function if it exists. If it doesn't try the formula below, this assumes a space between each word

Local StringVar Array Words;
Local NumberVar i;

Words := Split('this is my sentence to change', ' ');

For i := 1 to Ubound(Words)
Do(
Words[i] := UCase(Left(Words[i], 1)) & Right(Words[i], (Len(Words[i]) - 1)));

Join(Words, ' ')

Open in new window

0
 
lbeesonAuthor Commented:
I actually have 2 fields I would be using this for... A name field and an address field.  The data is stored in all caps
0
 
lbeesonAuthor Commented:
I don't see the ProperCase function in version 8 :(
0
 
GJParkerCommented:
You can create 2 new formulas using teh syntax I provided above replacing the 'this is my sentence to change' part of the formula with your fields
0
 
lbeesonAuthor Commented:
@GJParker - I am going to try your solution... I am surprised that it is not a built in function.  Another option maybe to format it in a view out of SQL Server (which is the database software used).
I will post soon to let you know.  Thanks so much!
0
 
James0628Commented:
Slight variation on GJParker's formula.  You can replace Right(Words[index], (Len(Words[index]) - 1)) with Mid(Words[index], 2).  It's simpler to read, and might even be a bit more efficient, although I doubt that it would actually have a significant effect on the performance of the formula.

 I replaced the "i" in the subscripts with "index", because EE was whining about it being some kind of tag and wouldn't let me post this with them there.  You'd need to change the "index" back to "i" to use that Mid function in that formula.

 James
0
 
lbeesonAuthor Commented:
Thanks James,
I think you lost me with the replacement of "i".

Let me be a little more descriptive in my issue.
I am connecting to SQL Server via ODBC connection to and Employee table that has FirstName and
LastName as field names.  The content of both of those fields is in all caps.

I am using the Crystal Reports "Formula Editor" to conjoin those fields like so:
{Employee.FirstName} & " " & {Employee.LastName}

I want the report to display Laura Beeson instead of LAURA BEESON.

In MSAccess it's super easy, I just say FirstLast: StrConv([Employee]![FirstName] & " " & [Employee]![LastName],3) an viola it displays as I wish.

Crystal Reports 8 just wants to be difficult.
0
 
GJParkerCommented:
This should do it

Left({Employee.FirstName}, 1) & LCase(Right({Employee.FirstName}, (Len({Employee.FirstName}) - 1))))
& ' ' &
Left({Employee.LastName}, 1) & LCase(Right({Employee.LastName}, (Len({Employee.FirstName}) - 1))))
0
 
lbeesonAuthor Commented:
Hmmm It says there is an error in that formula but I think we are getting closer...
I also have the same issue with the {Address1} field and that one can be more complicated since its not a single word and can contain an unknown number of numbers in the beginning.
Thank you so much for helping with this.  
0
 
GJParkerCommented:
Ovberdid it on the brackets, try this

Left({Employee.FirstName}, 1) & LCase(Right({Employee.FirstName}, (Len({Employee.FirstName}) - 1))
& ' ' &
Left({Employee.LastName}, 1) & LCase(Right({Employee.LastName}, (Len({Employee.FirstName}) - 1))

You should be able to use my earlier formula for the address field, if this doesn't work then provide an example of your addresses and how you want them to appear
0
 
lbeesonAuthor Commented:
Still getting an error in that formula. Says it's missing a ')'.
I will keep playing with it.
I am shocked Crystal doesn't offer am easier solution.
as for the address field(s)
The address Appears as 123 ABC ST.
And I would like it to appear as 123 Abc St.
The field name is Address1
0
 
lbeesonAuthor Commented:
Woot Woot - The First and Last Name part worked :)

So now I will work on the Adress

I have never used code like that in Crystal before so this will be interesting.
0
 
mlmccCommented:
To change to the other method

Left({Employee.FirstName}, 1) & LCase(Mid({Employee.FirstName}, 2))
& ' ' &
Left({Employee.LastName}, 1) & LCase(Mid({Employee.LastName},2))

mlmcc
0
 
James0628Connect With a Mentor Commented:
 > I think you lost me with the replacement of "i".

 FWIW, all I was saying was that where I had written

 Mid(Words[index], 2)

 you would need to replace the word "index" with the letter "i".  That was a modification for part of GJParker's formula, and he had used "i" in the brackets, but when I tried to post that message with the "i" in the brackets, EE (the Experts Exchange site) was trying to interpret it as a formatting tag for my post, and giving me an error because the tag wasn't used properly.  So, I replaced the "i" with "index", so that EE would let me post the message.  In order to use that code in GJParker's formula, you would have to change the "index" back to an "i" (without the quotes).

 It was supposed to just be a simple suggestion for a small change to GJParker's formula.  :-)  The problem with trying to include the "i" in brackets in my post just complicated things.

 And in case you're wondering, GJParker was able to use the "i" in the brackets presumably because he had posted that in a "code" block, whereas I was trying to include it in the body of a message.  I could have used a "code" block too, but I wasn't posting a block of code - Just a small change to someone else's code - so the "code" block thing didn't really seem appropriate.  And I'm stubborn.  :-)


 Anyway, getting back to your problem, you should be well on your way.  GJParker's formula should work, as long as the words that need to be capitalized are either the first word in the address, or follow at least one space.  What it does it split the address into separate "words" wherever there's a space, then convert the first character in each word into uppercase, and the remaining characters in each word into lowercase.  If the characters aren't letters, they won't be changed.  He used '123 ABC ST,' as a test address.  To use the formula on your address field, you'll need to change

Words := Split('123 ABC ST,', ' ');

   to

Words := Split({Employee.Address1}, ' ');


 James
0
 
James0628Commented:
Oh, yeah.  Another FWIW.

 > I am shocked Crystal doesn't offer am easier solution.

 CR 10 does.  The ProperCase function.  But apparently they hadn't implemented that function yet in CR 8 (which, to be fair, is quite old).

 James
0
 
lbeesonAuthor Commented:
Thanks so much :)
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.