Using Select Case wants an Else at the end 1

[cakoliver]

Using CRXI with an ODBC database, I'm trying to build a date range which depends on the day of the week of a date type parameter. The Date range will always be a Saturday to Friday Range. Once I know the day of the week, I can build my date range by adding to or subtracting from the date entered.
My code below seems pretty simple to me, however it produces an error indicating that the word "else" is missing at the very end of the code. I'm hoping that someone might see something I've left out. BTW - changing the entire thing to a big if/else structure results in same error...
Any Help would be greatly appreciated...

//Sun = 1 , Sat = 7 Client work week = Sat thru Friday or 7 thru 6

select DayOfWeek({?Work Day})

case 1 : shared datevar Range Week1:= cdate(DateAdd("d",-1 ,{?Work Day})) to cdate(DateAdd("d",5 ,{?Work Day}))
case 2 : shared datevar Range Week1:= cdate(DateAdd("d",-2 ,{?Work Day})) to cdate(DateAdd("d",4 ,{?Work Day}))
case 3 : shared datevar Range Week1:= cdate(DateAdd("d",-3 ,{?Work Day})) to cdate(DateAdd("d",3 ,{?Work Day}))
case 4 : shared datevar Range Week1:= cdate(DateAdd("d",-4 ,{?Work Day})) to cdate(DateAdd("d",2 ,{?Work Day}))
case 5 : shared datevar Range Week1:= cdate(DateAdd("d",-5 ,{?Work Day})) to cdate(DateAdd("d",1 ,{?Work Day}))
case 6 : shared datevar Range Week1:= cdate(DateAdd("d",-6 ,{?Work Day})) to cdate(DateAdd("d",0 ,{?Work Day}))
case 7 : shared datevar Range Week1:= cdate(DateAdd("d",-0 ,{?Work Day})) to cdate(DateAdd("d",6 ,{?Work Day}))

 

[lbass]

You can't specify a range as a formula result.

There is a simpler solution. If you want to use this in a record selection formula, use:

{table.date} in {?Work Day}-dayofweek({?Work Day},crSaturday)+1 to {?Work Day}-dayofweek({?Work Day},crSaturday)+7

-LB

 

[cakoliver]

LBass,
Thanks for your quick response and answer!
Your solution was much simpler than mine and works great.
It's not for record selection but to determine if a certain value belonged in the WTD column of a report.

Obviously, your code is cleaner but would there be a way to build that range(like I was attempting) and use it as I used your code below?

ie. My desired formula result is numeric, I just hadn't got that far...

if {PJF_FIELDREPORTS__FIELD_REPORT.Date}in {?Work Day}-dayofweek({?Work Day},crSaturday)+1 to {?Work Day}-dayofweek({?Work Day},crSaturday)+7
then
{PJF_FIELDREPORTS__WORK_ACTIVITIES.Production_Units_in_Place}
else
0

 

[lbass]

I guess I don't see why you would want to do that. In fact you can't, since a formula result cannot be a range. If you want to create separate formulas for the start and end of a range you could, but in that case I would just use:

//{@start}:
{?Work Day}-dayofweek({?Work Day},crSaturday)+1

//{@End}:
{?Work Day}-dayofweek({?Work Day},crSaturday)+7

{table.date} in {@Start} to {@End}

If you wanted to use your more complicated approach, again, you'd have to separate the beginning and end of the range:

//{@start}:
datevar start := (
select DayOfWeek({?Work Day})
case 1 : cdate(DateAdd("d",-1 ,{?Work Day})
case 2 : cdate(DateAdd("d",-2 ,{?Work Day})
//etc.
);

-LB

 

[cakoliver]

I was just thinking of other formulas I've done that load shared array variables, I always put some bogus value at the end as the result of a formula cannot be an array.
In this case I certainly wouldn't want to that, this was more for future reference...