Report Design
 Crystal Reports Forum : Crystal Reports .NET 2003 : Report Design
Message Icon Topic: Triming name problem Post Reply Post New Topic
Author Message
Krazy Kasper
Newbie
Newbie
Avatar

Joined: 11 Jul 2007
Location: United States
Online Status: Offline
Posts: 23
Quote Krazy Kasper Replybullet Topic: Triming name problem
    Posted: 14 Jan 2009 at 10:24am

The Order Rep Names in my ODBC table appear as follows, sometimes with all caps and sometimes not:

Lastname, Firstname OR Lastname, Mi. Firstname

I need to change them in my Crystal report to appear as follows with "normal" (first letter of each name capitalized:

Firstname Mi. Lastname

When I do the same thing in a VBA report my code is:

Function ParseFirstComp(OrderRepName) As String
ParseFirstComp = "" 'set the default return value
If Not IsNull(OrderRepName) Then
  Dim LPosition As Integer
  'Find postion of space
  LPosition = InStr(OrderRepName, " ")
  'Return the portion of the string before the space
  If LPosition > 0 Then
    ParseFirstComp = Left(OrderRepName, LPosition - 2)
  End If
End If
End Function

Function ParseSecondComp(OrderRepName) As String
ParseSecondComp = "" 'set the default return value
If Not IsNull(OrderRepName) Then
  Dim LPosition As Integer
  'Find postion of space
  LPosition = InStr(OrderRepName, " ")
  'Return the portion of the string after the space
  If LPosition > 0 Then
    ParseSecondComp = Mid(OrderRepName, LPosition + 1)
  End If
End If
End Function

And then in my query:

Trim ({OptimizeIt.OrderRepName}) ([Expr2] & "  " & [Expr1])

Can someone provide me the code so I can do the same in my Crystal Report? I expect I would put the code in a formula.

Thanks,

 Krazy (Bill) Kasper

Krazy Kasper
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 20 Jan 2009 at 2:20pm
This is actually very easy - Create a formula and use the ProperCase() function in Crystal.  From the Crystal Help file:
 
"ProperCase capitalizes the first letter of each word in the string and converts all other letters to lower case. It also capitalizes any letter that follows a non-alpha character.

Tip:    This function is particularly useful for capitalizing people's names correctly when they are stored as all capitals in a database."

-Dell
IP IP Logged
Krazy Kasper
Newbie
Newbie
Avatar

Joined: 11 Jul 2007
Location: United States
Online Status: Offline
Posts: 23
Quote Krazy Kasper Replybullet Posted: 21 Jan 2009 at 5:17am
Thanks very much for your help.
Krazy Kasper
IP IP Logged
Krazy Kasper
Newbie
Newbie
Avatar

Joined: 11 Jul 2007
Location: United States
Online Status: Offline
Posts: 23
Quote Krazy Kasper Replybullet Posted: 27 Jan 2009 at 5:43am
OK, here's where I'm at.
 
The structure is lastname, firstname middleintital.
There is not always a middleinitial.

The code I'm using (follows) only gives me the middleinitial:

local StringVar Array Names1:=
Split({OptimizeIt.OrderRepName}, " ");
If UBound(Names1)=3 Then ProperCase(Names1[3]) Else " "


Changing it to the following only gives me the firstname:

local StringVar Array Names1:=
Split({OptimizeIt.OrderRepName}, " ");
If UBound(Names1)=2 Then ProperCase(Names1[2]) Else " "

Changing it to the following gives me nothing:
(You'd think it would give me the lastname):

local StringVar Array Names1:=
Split({OptimizeIt.OrderRepName}, " ");
If UBound(Names1)=1 Then ProperCase(Names1[1]) Else " "

Can someone help me put this all together so I get firstname middleinitial lastname (and no comma)?
Thanks.



Edited by Krazy Kasper - 27 Jan 2009 at 5:51am
Krazy Kasper
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 27 Jan 2009 at 6:51am
Try something like this:
ProperCase(trim(Mid({OptimizeId.OrderRepName}, InStr({OptimizeId.OrderRepName}, ',') + 1)) + " " +
trim(left({OptimizeId.OrderRepName}, InStr({OptimizeId.OrderRepName}, ',') -1)))
 
-Dell
IP IP Logged
Krazy Kasper
Newbie
Newbie
Avatar

Joined: 11 Jul 2007
Location: United States
Online Status: Offline
Posts: 23
Quote Krazy Kasper Replybullet Posted: 27 Jan 2009 at 8:19am
Thanks. I'll give it a shot.
Krazy Kasper
IP IP Logged
Saleem Muhammad
Newbie
Newbie


Joined: 25 Aug 2009
Online Status: Offline
Posts: 1
Quote Saleem Muhammad Replybullet Posted: 25 Aug 2009 at 7:41am

Please help in getting the proper name format using propercase function for names like MCDONALD (should appear like McDonald)

Saleem
IP IP Logged
Krazy Kasper
Newbie
Newbie
Avatar

Joined: 11 Jul 2007
Location: United States
Online Status: Offline
Posts: 23
Quote Krazy Kasper Replybullet Posted: 25 Aug 2009 at 2:03pm
I have never seen any solution to the question you pose.
Perhaps one of the guru's can help.
 
 
Krazy Kasper
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.