What expression is needed to select date&time 3 times a day?

[jo]

I have a report that shows items not checked in the last 8 hours. But now I
would like another report to show what was checked between the following
shifts:
6am-2pm
2pm-10pm
10pm-6am
Can this be done?

 

[Stefan Hoffmann]

hi Jo,

I have a report that shows items not checked in the last 8 hours. But now I
would like another report to show what was checked between the following
shifts:
6am-2pm
2pm-10pm
10pm-6am
Can this be done?

Yes, you need the Partition() function as group expression:

=Partition(Hour([checkedAt])*60+Minute([checkedAt]);0;1440;480)

The only thing you need to adjust is shift offset. So instead of the
time value use:

DateAdd("h";-6;[checkedAt])

Or use it in a query.


mfG
--> stefan <--

 

[jo]

Hi Stefan thanks for your reply can I add this to a query as an expression
and where it says [checkAt] what would I put in that the time?

hi Jo,

I have a report that shows items not checked in the last 8 hours. But now I
would like another report to show what was checked between the following
shifts:
6am-2pm
2pm-10pm
10pm-6am
Can this be done?

Yes, you need the Partition() function as group expression:

=Partition(Hour([checkedAt])*60+Minute([checkedAt]);0;1440;480)

The only thing you need to adjust is shift offset. So instead of the
time value use:

DateAdd("h";-6;[checkedAt])

Or use it in a query.


mfG
--> stefan <--
.

 

[Stefan Hoffmann]

hi Jo,

Hi Stefan thanks for your reply can I add this to a query as an expression
and where it says [checkAt] what would I put in that the time?

Yes, copy it into the field column like this:

Shift:
Partition(Hour(DateAdd("h";-6;[checkedAt]))*60+Minute(DateAdd("h";-6;[checkedAt]));0;1440;480)

And replace [checkedAt] with your field name.

As SQL:

SELECT *,
Partition(Hour(DateAdd("h";-6;[checkedAt]))*60+Minute(DateAdd("h";-6;[checkedAt]));0;1440;480)
AS [Shift]
FROM [yourTable]


mfG
--> stefan <--