Solved

Crystal Reports 8 - Using TitleCase

Posted on 2011-03-01
20
1,738 Views
Last Modified: 2012-05-11
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
Comment
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
  • Learn & ask questions
  • 8
  • 5
  • 5
  • +1
20 Comments
 
LVL 101

Expert Comment

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

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
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

by:GJParker
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, ' ')

Open in new window

0
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 

Author Comment

by:lbeeson
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

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

Expert Comment

by:GJParker
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

by:lbeeson
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

by:James0628
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

by:lbeeson
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

by:GJParker
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

by:lbeeson
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

by:GJParker
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

by:lbeeson
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.
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
 
LVL 19

Accepted Solution

by:
GJParker earned 175 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)))

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
 

Author Comment

by:lbeeson
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

by:mlmcc
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

by:James0628
James0628 earned 75 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

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


 James
0
 
LVL 35

Expert Comment

by:James0628
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

by:lbeeson
ID: 35037872
Thanks so much :)
0
 
LVL 35

Expert Comment

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

 James
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question