Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Crystal Reports 8 - Using TitleCase

Posted on 2011-03-01
Medium Priority
1,806 Views
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?
0
Question by:lbeeson
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 8
• 5
• 5
• +1

LVL 101

Expert Comment

ID: 35014144
Other than the first word is there a space before each word?

mlmcc
0

LVL 35

Expert Comment

ID: 35015638
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

LVL 19

Expert Comment

ID: 35016224
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, ' ')
``````
0

Author Comment

ID: 35018564
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

Author Comment

ID: 35018574
I don't see the ProperCase function in version 8 :(
0

LVL 19

Expert Comment

ID: 35018594
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

Author Comment

ID: 35018644
@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

LVL 35

Expert Comment

ID: 35025002
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

Author Comment

ID: 35029261
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

LVL 19

Expert Comment

ID: 35029330
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

Author Comment

ID: 35029378
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

LVL 19

Expert Comment

ID: 35029464
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

Author Comment

ID: 35029568
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.
The address Appears as 123 ABC ST.
And I would like it to appear as 123 Abc St.
0

LVL 19

Accepted Solution

GJParker earned 700 total points
ID: 35029634
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)))

``````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, ' ')
``````
0

Author Comment

ID: 35029695
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

LVL 101

Expert Comment

ID: 35031857
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

LVL 35

Assisted Solution

James0628 earned 300 total points
ID: 35035123
> 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

James
0

LVL 35

Expert Comment

ID: 35035131
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

Author Closing Comment

ID: 35037872
Thanks so much :)
0

LVL 35

Expert Comment

ID: 35043149
You're welcome.  Glad I could help.

James
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater â€¦
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearlyâ€¦
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonstâ€¦
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/weâ€¦
###### Suggested Courses
Course of the Month9 days, 16 hours left to enroll