Crystal Reports 2008 - First position in field is " "

Hi and thanks,

I have this code:
If {TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress} > "" Then
Left({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}, Instr({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}, " ") -1)
Else "";

This works fine until there is a bland in the first position of the field Example:

|1234 Church Street| <== this works fine and gives me |1234|
| 1234 Church Street| <=== This give me a return that is blank

Also:
I would like to find the formula for:
|1234 Church Street| = |Church Street|

Thanks
Amour22015Asked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
This should take care of your blank value:

If {TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress} > "" Then 
Left(LTRIM({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}), Instr(LTRIM({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}), " ") -1)
Else "";

Open in new window

0
 
CodeCruiserCommented:
Should

If {TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress} > "" Then

not be

If {TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress} <> "" Then
0
 
mlmccCommented:
Does the text always have the | | around it or did you add those for clarity?

mlmcc
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Amour22015Author Commented:
mlmcc,
added || for clarity
0
 
mlmccConnect With a Mentor Commented:
You could also use

If IsNull({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}) OR Trim({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}) = ""  THEN
   ""
Else
    Split(Trim({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress})," ")[1]

mlm
0
 
James0628Connect With a Mentor Commented:
For your other question, to get the part of the string after the first space, you could use:

if InStr (Trim ({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}), " ") > 0 then
  Mid (Trim ({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}),
   InStr (Trim ({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}), " ") + 1)
else
  {TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}


 Or, you could use a variable to eliminate the repeated Trim functions:

Local StringVar str;

str := Trim ({TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress});

if InStr (str, " ") > 0 then
  Mid (str, InStr (str, " ") + 1)
else
  {TEMP_PWC_Sales_Report_SP_MAIN.OwnerAddress}


 In each formula, the last line, after the "else", is what you want the formula to produce if the field doesn't contain any embedded spaces.  For example, if it was just "Church", would you want to see the original field ("Church"), or nothing, or something else?  Change that last line accordingly.

 I am assuming that the field won't be null, or the report is set to convert nulls to default values.  If not, you'll need to use IsNull to handle them.

 James
0
 
Amour22015Author Commented:
Thanks for all your help...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.