datetime evaluation

[butkus]

I'm trying to develope a report that will run court dockets.

Different courts have dockets at different time during the day.

The users of the report will be able to select Docket Date / Court / and Docket Time from 3 different parameters.

I need to handle potential data entry errors in the Docket Time portion of the record selection.

Example: if a court coordinator inputs a 1:31 for the 1:30 docket, I need the reports to find it and return it in the 1:30 docket.

The docket time are 8:30am / 9:00am / 1:30 pm / 2:00 pm / 6:00 pm

below is my record selection formula (which does not work BTW):

if isnull({fcl_cal_schd_itm.delete_flg}) or {fcl_cal_schd_itm.delete_flg} ="N" 
then
if {fcl_calndr_actvty.org_id} = {?SetCourt} 
then
select{?Docket Time}
case "8:30 am" :
{fcl_cal_schd_matr.app_tm} in datetime({?DocketDate},time(08,30,00)) to datetime({?DocketDate},time(08,59,00))
case "9:00 am" :
{fcl_cal_schd_matr.app_tm} in datetime({?DocketDate},time(09,00,00)) to datetime({?DocketDate},time(13,29,00))
case "1:30 pm" :
{fcl_cal_schd_matr.app_tm} in datetime({?DocketDate},time(13,30,00)) to datetime({?DocketDate},time(13,59,00))
case "2:00 pm" :
{fcl_cal_schd_matr.app_tm} in datetime({?DocketDate},time(14,00,00)) to datetime({?DocketDate},time(17,59,00))
case "6:00 pm" :
{fcl_cal_schd_matr.app_tm} in datetime({?DocketDate},time(18,00,00)) to datetime({?DocketDate},time(23,59,00))

writing in CR 8.5 dev against an Informix DB.
thanks in advance for the assistance.

James

 

[lbass]

Is {fcl_cal_schd_matr.app_tm} a datetime field? Not sure what your intent is here, but you might try changing the formula to:

(
isnull({fcl_cal_schd_itm.delete_flg}) or
{fcl_cal_schd_itm.delete_flg} ="N"
) and
{fcl_calndr_actvty.org_id} = {?SetCourt}
and
select{?Docket Time}
case "8:30 am" :
{fcl_cal_schd_matr.app_tm} in datetime({?DocketDate},time(08,30,00)) to datetime({?DocketDate},time(08,59,00))
case "9:00 am" :
{fcl_cal_schd_matr.app_tm} in datetime({?DocketDate},time(09,00,00)) to datetime({?DocketDate},time(13,29,00))
case "1:30 pm" :
{fcl_cal_schd_matr.app_tm} in datetime({?DocketDate},time(13,30,00)) to datetime({?DocketDate},time(13,59,00))
case "2:00 pm" :
{fcl_cal_schd_matr.app_tm} in datetime({?DocketDate},time(14,00,00)) to datetime({?DocketDate},time(17,59,00))
case "6:00 pm" :
{fcl_cal_schd_matr.app_tm} in datetime({?DocketDate},time(18,00,00)) to datetime({?DocketDate},time(23,59,00))
default : datetime(0,0,0,0,0,0)

-LB