Changing value of field if field contains a certain word

[Stewman]

Hello,

I am using crystal 10 and working on a report where I have a name field that is using a formula that combines first and last name columns from my view. However, some records don't have a first name, just a last name of Unknown followed by a case number. So the field may look like UNKNOWN 20070000000. What I want is that field with the use of a formula to just show Unknown if this is the case.

I've tried if then else logic and looked at decode function and can't get anything to work. Any help appreciated.

Thanks,
Chris

 

[Kendel]

You can do something like:

If lastname="Unknown" then
fullname = "Unknown"
else
fullname = lastname + ", " + firstname

 

[dgillz]

If left({YourField},7)="UNKNOWN" then "UNKNOWN" else {YourField}

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Stewman]

Well this is what I have tried and it doesn't work. I do appreciate the tip though. Let me restate my situation and hopefully this clears some things up.

I have created a field called name which is not part of my view. I take the first and last names which is 2 columns in my view (first & last) and combined that into a column on my report called name. Basically another way to achieve what I want is to be able to remove the case number that appears and just leave UNKNOWN. So the data will say UNKOWN 20070000000. I want to remove the #. Any tips greatly appreciated.

 

[Stewman]

dgillz, you are the man. That worked perfectly. I didn't see your post till after I replied. Thanks for your help.

 

[dgillz]

Stewman.

You should not change the database or view in any way, shape, matter or form. Crystal does not typically require database changes.

In crystal reports designer, go to the field explorer, right click on formula field, select new, and paste my formula in. Replace {YourField} with the database field that contains "UNKNOWN 2000700000000'.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry