TRIM Formula 1

[Shtinky]

Hello everyone,

CR 8.5/Access DB

I am writing a report for our Sales team. The report is basically a listing of our Sales Reps, and the customers assigned to each rep.

The report is based on a single table, {Customerlist1}

I am having a problem displaying the customer name for each customer record. The database has a design flaw, which is the reason I am encountering problems. The customer name and a hyperlink to their website or an excel spreadsheet is stored in the same field{Customerlist1.NAME} - string field.

When I run a simple report with {Customerlist1.NAME} as the only field in the details tab, each record looks something like this:
CENTRAL WORLDWIDE#CENTRA WORLDWIDE.xls#

I need to get rid of the #CENTRA WORLDWIDE.xls# portion of the string, and just display CENTRAL WORLDWIDE. I hope to accomplish this through a TRIM.

Here is my TRIM:
Left({Customerlist1.NAME},InStr({Customerlist1.NAME},"#")-1)

and here is my formula (Crystal Syntax)...

if isnull({Customerlist1.NAME})
then
{Customerlist1.NAME}
else
Left({Customerlist1.NAME},InStr({Customerlist1.NAME},"#")-1)

This formula works fine until Crystal finds a {Customerlist1.NAME} record with no hyperlink attached. When Crystal finds a record with no hyperlink, I get an error stating "Sting length is less than 0 or not an integer".

I need to expand my formula via another if then statement to allow for a {Customerlist1.NAME} with no hyperlink attached:

if {Customerlist1.NAME} doesn't contain a hyperlink (or just doesn't contain a "#" symbol) then {Customerlist1.NAME}

I have no clue how to add this portion to my formula. I any of you guru's out there have any ideas, please feel free to comment.

I sincerely appreciate your time.

The Shtinkster

 

[campsys]

if not ({Customerlist1.NAME} like "*#*") then {Customerlist1.NAME}

 

[Reebo99]

Or:

If InStr({Customerlist1.NAME},"#") = 0

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.