Newbie needs help on how to setup CR formula using VB .Net

[rkckjk]

Being sort of new to using Crystal Reports in VB .Net...I need help on how to setup and use formulas in CR with VB .Net. I have a sample of a CR I setup in VB .Net. The field name is 'Open' in my test.rpt. It's basically a date and time in the 24 hour(military format). The formula I need is to count the total on how many 'Open' field objects there are in a first shift time frame(8:01 - 16:00), second shift(16:01 - 00:00) and third shift(00:01 - 8:00) for each day of a month.

The example below is just a snippet of data for the month of March. So, based on what data I pasted here, the results would be the following:

3/1/2005 first shift: 12 items second shift: 3 items third shift: 0 items
3/2/2005 first shift: 5 items second shift: 1 items third shift: 3 items

3/1/2005 7:32
3/1/2005 7:50
3/1/2005 7:51
3/1/2005 9:23
3/1/2005 9:53
3/1/2005 9:35
3/1/2005 9:41
3/1/2005 12:45
3/1/2005 13:50
3/1/2005 13:58
3/1/2005 14:58
3/1/2005 15:09
3/1/2005 18:58
3/1/2005 21:08
3/1/2005 23:09

3/2/2005 3:16
3/2/2005 3:58
3/2/2005 7:58
3/2/2005 8:09
3/2/2005 8:16
3/2/2005 13:58
3/2/2005 14:58
3/2/2005 15:09
3/2/2005 18:58
.
.
and so on

Thanks

 

[lbass]

Create a formula {@Shift}:

if time({table.opendatetime}) in time(0,0,1) to time(8,0,0) then "Third Shift" else
if time({table.opendatetime}) in time(8,0,1) to time(16,0,0) then "First Shift" else
if time({table.opendatetime}) in time(16,0,1) to time(0,0,0) then "Second Shift"

Then insert a group on {table.opendatetime} and set it for "Print on change of day". Then insert a group on {@Shift}. Finally, you should right click on {table.opendatetime} in the detail section and insert a summary (count) at the group level.

-LB

 

[rkckjk]

It works great except it doesn't display "Second Shift

 

[lbass]

I think it has to do with the time(0,0,0) belonging to the next day (in fact), and you are trying to count it toward the previous day. Logically, the formula should instead be:

if time({table.opendatetime}) in time(0,0,0) to time(7,59,59) then "Third Shift" else
if time({table.opendatetime}) in time(8,0,0) to time(15,59,59) then "First Shift" else
if time({table.opendatetime}) in time(16,0,0) to time(23,59,59) then "Second Shift"

I think the last clause in my previous suggestion probably confused the logical time progression and was read as time(0,0,0) to time(16,0,1). If you need the breaks between shifts as originally stated, then dates would have to be factored into the formula.

-LB