Selecting Off-Hours System Access

[v45]

In Crystal 8.5, I'm trying to select records for off-hours system access. The tables have a login date/time field. The report needs to run from the current date, usually Monday, and include the previous week's access for after 5:00 p.m. until 8:00 a.m. Monday thru Friday and all weekend. I'm able to get it to work for the weekend using the DayOfWeek function but have not been able to figure out the weekdays.

Would it be easier to just exclude work hours?

Any pointers are appreciated.

 

[kskid]

Try this in your record selection

(
(
DayofWeek(logindate) in [2 to 6] and not(hour(logindate) in [8 to 17]
) or
DayofWeek(logindate) in [1,7]
)


Cheers,
-LW

 

[MrBillSC]

What does your output need to look like and what does your select formula look like so far?
MrBill

 

[v45]

Wichita Kid nailed it - - thanks

The selection formula is:

(
DayofWeek({Login_History.start_datetime}) in [2 to 6] and not(hour({Login_History.start_datetime}) in [8 to 17])

or

DayofWeek({Login_History.start_datetime}) in [1,7]
)
and
(

{Login_History.start_datetime} in {@Start of Week} to {@End of Week}

)

Where @Start of Week is:
If DayOfWeek (CurrentDate) = 1 //Sunday
then DateAdd ("d", -6 , DateTime (CurrentDate) ) else
If DayOfWeek (CurrentDate) = 2 //Monday
then DateAdd ("d", -7 , DateTime (CurrentDate) ) else
If DayOfWeek (CurrentDate) = 3 //Tuesday
then DateAdd ("d", -8 , DateTime (CurrentDate) ) else
If DayOfWeek (CurrentDate) = 4 //Wednesday
then DateAdd ("d", -9 , DateTime (CurrentDate) ) else
If DayOfWeek (CurrentDate) = 5 //Thursday
then DateAdd ("d", -10 , DateTime (CurrentDate) ) else
If DayOfWeek (CurrentDate) = 6 //Friday
then DateAdd ("d", -11 , DateTime (CurrentDate) ) else
If DayOfWeek (CurrentDate) = 7 //Saturday
then DateAdd ("d", -12 , DateTime (CurrentDate) ) else CurrentDateTime



The @End of Week is similar, but 7 days later...

thanks