Sorting times starting at 6 a.m. instead of midnight 2

[JohnnyD]

I have created a table of deliveries to an assembly plant. The plant runs from 06:00 to 04:30 (a.m. to a.m.) each day. When I run the queries from the table or try to creat a report from the query the times always sort from 00:30 on through the day. I want to start the sort at 06:00 instead of midnight. How in the world do I do this?

 

[raskew]

In a query that sorts on DelDate and DelTime (both sorted Ascending), add a calculated field-

shiftdate: IIf(Hour([DelTime])<6,[DelDate]-1,[DelDate])

Place this before DelDate in your query, and set sort to Ascending. The dates/times will now sort as you described.

NOTE: In my example, I used European/military time.

 

[MichaelRed]

Another approach would be to create an additional date field in the query w/ an offset of six hours. Sort by this date, but don't display it.

SortDate: DateAdd(&quot;h&quot;, -6, [DelDate])

Sort Ascending on this col - BUT DO NOT display it.

The advantage here is that the displayed date is not altered.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over