How to Combine Two Fields: Numeric and String

[Former Member]

Trying to obtain the result in Bold below

IF {'Sales_Data_'.Product Code} STARTSWITH "BR"

THEN {'Sales_Data_'.Qty} + Bottle (if singular) or Bottles (if more than one)

ELSE IF NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR")

THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} + Case (if one) or Cases (if more than one)

ELSE IF {'Sales_Data_'.Product Code} STARTSWITH "DP"

THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case}

+ Case (if one) or Cases (if more than one)

[Former Member · Accepted solution]

Hello

I figured it out!

Found the solution here

http://stackoverflow.com/questions/25767315/crystal-reports-trying-to-use-startswith-and-looking-for...

The solution is

STARTSWITH ["BR","DP"]

then I fiddled with the parenthesis.

Thank you all for your help!

[Former Member]

Hi Abhilash and Brian

Thank you for your help on this matter!

[Former Member]

Thank you Brian.

After amending the formula as per your instruction, I get an error message that "A number, currency amound, boolean, date, time, date-time, or string is expected here" on the bold below:

IF {'Sales_Data_'.Product Code} STARTSWITH "BR"
THEN {'Sales_Data_'.Qty} & (if{'Sales_Data_'.Qty} = 1 then " Bottle" Else " Bottles")
ELSE IF NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR") OR STARTSWITH "DP"
THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} & if({'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} = 1 then " Case" Else " Cases")

[former_member292966]

Hi,

On the second line, the open bracket should be before the IF like:

(IF {'Sales_Data_'.Qty} = 1 THEN "Bottle" ELSE " Bottles")

Brian

[Former Member]

Thank you Brian.

After amending the formula as per your instruction, I get an error message that "A number, currency amound, boolean, date, time, date-time, or string is expected here" on the bold below:

IF {'Sales_Data_'.Product Code} STARTSWITH "BR"
THEN {'Sales_Data_'.Qty} & (if{'Sales_Data_'.Qty} = 1 then " Bottle" Else " Bottles")
ELSE IF NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR") OR STARTSWITH "DP"
THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} & if({'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} = 1 then " Case" Else " Cases")

[former_member292966]

Hi,

You need to encapsulate the entire IF condition in brackets like:

ELSE IF (NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR") OR STARTSWITH "DP")

Otherwise the logic expects the Or to be a new condition.

Brian

[Former Member]

I suppose it has to do with either misplaced parenthesis or missing curly brackets somewhere, but I can't figure out where...

[Former Member]

Thank you again.

Upon checking it for error before saving, I get the following error message:

"The ) is missing" on the bold THEN below.

IF {'Sales_Data_'.Product Code} STARTSWITH "BR"

THEN {'Sales_Data_'.Qty} & IF ({'Sales_Data_'.Qty} = 1 THEN "Bottle" ELSE " Bottles")

ELSE IF NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR") OR STARTSWITH "DP"

THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} & IF ({'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} = 1

THEN " Case" ELSE (" Cases")

[abhilash_kumar]

Hi Rony,

Try:

IF {'Sales_Data_'.Product Code} STARTSWITH "BR"
THEN {'Sales_Data_'.Qty} & if({'Sales_Data_'.Qty} = 1 then " Bottle" Else " Bottles")
ELSE IF NOT ({'Sales_Data_'.Product Code} STARTSWITH "BR") OR STARTSWITH "DP" 
THEN {'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} & if({'Sales_Data_'.Qty} * {'Bacchus_Vinos_Costing_Sheet_'.Units_per_Case} = 1 then " Case" Else " Cases")

-Abhilash