Conditional Record Selection Formula

[HouDog]

Hi all,

Does anyone know if you can have an If statement inside a record selection formula?

eg. if a = b then
selection formula 1
else if a = c then
selection formula 2
else
selection formula 3

 

[pgtek]

hi
yes u can

cheers

pgtek

 

[HouDog]

Hi, here is my statement. it give me an error saying that I am missing a &quot" on the first line on the 1

Thanks, Jae.

if ({?DateRange}(1) = Date(1900,1,1)) then
(IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = DateAdd (&quot;d&quot;, -1, CurrentDate)
else if ({?DateRange}(1) = Date(1900,1,2)) then
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = WeekToDateFromSun
else if ({?DateRange}(1) = Date(1900,1,3)) then
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = Last7Days
else if ({?DateRange}(1) = Date(1900,1,10)) then
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = MonthToDate
else if ({?DateRange}(1) = Date(1900,1,15)) then
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = CurrentDate
else if ({?DateRange}(1) = Date(1900,1,20)) then
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = YearToDate
else
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} in {?DateRange}

 

[kenhamady]

The problem is that you need square brackets to substring a multiple value parameter. Try:

if ({?DateRange} [1] =

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[HouDog]

Thanks Ken, that was it. Strangely enough, when I used that in regular formulas, I had to use {?DateRange)(1).

Thanks for your help.

 

[kenhamady]

I don't think you can use parens in a Crystal Substring. You are sure that was in CR?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[HouDog]

Ken, I am using CR 9.0. My report parameter ?DateRange is set to accept multiple values. I made a simple formula field on the report containing...

formula = totext(minimum({?DateRange}(1)))

When I run the report, it displays the correct value.

Jae.

 

[kenhamady]

That is because you are using 'Basic' syntax, not Crystal syntax, in your formula field. The selection formula has to be in Crystal syntax.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[HouDog]

ok i changed my selection formula to the following but when I select Database | Show SQL Query, it does not reflect my formula. My formula saved ok...does this mean that my SQL Server doesn't like the formula ??

Even the IsNull line is not showing up.

Jae.

numbervar myYear := Year(minimum({?DateRange}[1]));
numbervar myMonth := Month(minimum({?DateRange}[1]));
numbervar myDay := Day(minimum({?DateRange}[1]));
datevar myDate := Date(myYear,myMonth,myDay);

(IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
(
if myDate = Date(1900,1,1) then
{Orders.orderdate} = CurrentDate
else if myDate = Date(1900,1,2) then
{Orders.orderdate} = DateAdd (&quot;d&quot;, -1, CurrentDate)
else if myDate = Date(1900,1,3) then
{Orders.orderdate} = WeekToDateFromSun
else if myDate = Date(1900,1,10) then
{Orders.orderdate} = Last7Days
else if myDate = Date(1900,1,15) then
{Orders.orderdate} = MonthToDate
else if myDate = Date(1900,1,20) then
if Month(CurrentDate) in [1,2,3] then
{Orders.orderdate} = Calendar1stQtr
else if Month(CurrentDate) in [4,5,6] then
{Orders.orderdate} = Calendar2ndQtr
else if Month(CurrentDate) in [7,8,9] then
{Orders.orderdate} = Calendar3rdQtr
else
{Orders.orderdate} = Calendar4thQtr
else if myDate = Date(1900,1,25) then
{Orders.orderdate} = YearToDate
else
{Orders.orderdate} in {?DateRange}
)

 

[kenhamady]

Many Crystal functions can't be converted to SQL, which forces the processing to be local. All of your Date functions, DATE, YEAR, MONTH, etc. have to be processsed locally.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[HouDog]

Thanks Ken,

I was interpreting the results of my report incorrectly. My selection statement is working correctly.

Thanks for your input.
Jae.

 

[HouDog]

Thanks all who responded...it's working very nicely now.
Jae.