IF Statement 1

[JaneKOS]

I have 2 years worth of forecasts stored in my MSC_TBL. I've written a crystal report to pull through quantities forecast by month, starting with the current month and showing a rolling 12 months. Using the following I'm returning both 2010 & 2011 values. I want to bring back only 2010 if it exists, if not then only 2011 values.

if month({MSC_TBL.MSC_DUE_DATE})={@month1}AND
YEAR ({MSC_TBL.MSC_DUE_DATE})=YEAR(TODAY)then{MSC_TBL.MSC_ORIG_QTY}
else
if month({MSC_TBL.MSC_DUE_DATE})={@month1}AND
year ({MSC_TBL.MSC_DUE_DATE})=(year(today)+1)then {MSC_TBL.MSC_ORIG_QTY}
else 0

Any help greatly appreciated.

 

[BeeBear]

Hi,

Does your field {MSC_TBL.MSC_DUE_DATE} contain a list of dates, with one line per forecast?
If so, then your formula above will only look at the data on each line, and won't know whether there is a 2010 date aswell as a 2011 date.

How about splitting your formula into 2 formulas:

{@FormulaYr2010}
if month({MSC_TBL.MSC_DUE_DATE})={@month1}AND
YEAR ({MSC_TBL.MSC_DUE_DATE})=YEAR(TODAY)then{MSC_TBL.MSC_ORIG_QTY}
else
0


{@FormulaYr2011}
if month({MSC_TBL.MSC_DUE_DATE})={@month1}AND
YEAR ({MSC_TBL.MSC_DUE_DATE})=YEAR(TODAY)then{MSC_TBL.MSC_ORIG_QTY}
else
0
Put both these formulas in the report (hidden), then use a 3rd formula to display the required data:

{@YearComparison}

if {@FormulaYr2010}<> 0 then {@FormulaYr2010}
else {@FormulaYr2011}


Note - not sure what you want to display if there is no data for 2010, so the ELSE part of this last formula might need changing.

Also, you havent said what the formula {@month1} does.

If I'm completely off track here, you may need to put a sample of some lines of data, and the expected result?

Hope that helps at least


regards

BB

*** Count your blessings not your problems******

 

[JaneKOS]

Hi BeeBear,

Splitting into 2 formula's then testing on the year has worked perfectly, thank you.

Jane.