DateTimeField-Need Specific Date for a Specific Time Frame

[kateri36]

I have a database that has all physicians on call for a specific date & time. I need to create 3 different reports for the following shifts 7am-3pm, 3pm-11pm, & 11pm-7am(next day). My formula appears to be working for the 11pm-7am(next day)but it does not display all data, some appears to be missing and I can’t get a formula to work at all for the other shifts that are on the same day.

I have a parameter for {?date} & then two formula fields as shown below.

Note: I created two formula fields to be used in my "selection criteria"
//After 11pm-to retrieve all docs on call after 11pm for the date selected.

datevar after11:={?Date};
datetime(year(after11),month(after11),day(after11),22,59,59)

//Before 7AM-to retrieve all docs on call before 7am the following day for the date selected.
dateVar before7:={?Date}+1;
datetime(year(before7),month(before7),day(before7),07,00,00)

FORMULA:
{@Date}={?Date} and
({vw_oc_sel_allprsnphone.OnCallToDate} in {@after 11pm} to {@before 7am}) and
({vw_oc_sel_allprsnphone.OnCallFromDate} in {@after 11pm} to {@before 7am})

When I run the report I receive data for docs that are on call for that particular period, although....not all are appearing. When someone enters that on call date & time’s in the particular application they can enter one day at or a time or they can enter it for a specific period. Therefore, you could essentially have one record entry or multiple record entries. October 2, 2002 7am to October 10, 2002 11pm or it could be October 2, 2002 7am – 11pm, October 3, 2002 12:00am to 12:00am, October 4th……etc.

Can someone assist me with my formula? I’m working with a “datetime” field. In addition a formula to retrieve the docs on call where the time is within the date selected and doesn’t fall into a different date (next day or day after)

 

[synapsevampire]

First, formulas which contain variables generally will NOT get passed to the database (check the Database->Show SQL Query). I understand the urge to use them for some people, but you don't have to, and they'll slow things down.

What kind of database are you using?

What version of Crystal?

Is this the record selection criteria?:

FORMULA:
{@Date}={?Date} and
({vw_oc_sel_allprsnphone.OnCallToDate} in {@after 11pm} to {@before 7am}) and
({vw_oc_sel_allprsnphone.OnCallFromDate} in {@after 11pm} to {@before 7am})

I think that you want to change the criteria to:

{vw_oc_sel_allprsnphone.OnCallFromDate} in {?date}
and
{vw_oc_sel_allprsnphone.OnCallToDate} in {?date}
and
hour({vw_oc_sel_allprsnphone.OnCallFromDate}) in [23,24]
and
hour({vw_oc_sel_allprsnphone.OnCallFromDate}) in [0 to 7]
and
hour({vw_oc_sel_allprsnphone.OnCallToDate}) in [23,24]
and
hour({vw_oc_sel_allprsnphone.OnCallToDate}) in [0 to 7]

Keep in mind that this will NOT show the 11 to 12 for the first date as it's in the previous day, but it will show the 0 to 7 hours. And on the last date, it WILL show the 11 to 12.

The easiest means to corrent this is to make the {?date} a datetime and have it start with 11PM and end with 7AM

I think that you could also add an IF for each date to allow for this.

I think that this works...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com