Time Sort that crosses midnight

[poucedeleon]

Record Sort using Time/Date, that crosses midnight

The purpose of this application is to log Arresting officers coming into our facility. The Booking Assistant logs the time arrived, the officer’s name, the prisoner’s name, the time they give us their paperwork and the time they leave.
The problem I am having is I need to be able to do a sort by time so the Booking Assistant can look at the bookings by shift (This is a continuous form). The graveyard shift starts at 00:00hrs but the count for their shift starts at 23:00hrs the previous day and ends at 05:00hrs. I have not been able to create an expression to handle this.
The form has a field [BookedDate] (Formatted Date() ), and there are three time related fields that the Booking Assistants enter “TimeIn”, “PaperReceived” and “TimeOut”

In the Form Header I have an Option Group that will be used to sort the records by shift. I am using VBA code to make this work.

Private Sub cmdFilterRecords_Click()

'Variable to hold filtered SQL string
Dim strFilterSQL As String

'Set default record source of form
'Const strSQL = "SELECT tblStudentInformation.strShift,tblStudentInformation.strStudentID,tblStudentInformation.strFirstName,tblStudentInformation.strLastName,tblStudentInformation.strCity, tblStudentInformation.strCounty, tblStudentInformation.dtmEnrolled, tblStudentInformation.strSigninTime FROM tblStudentInformation"

    Select Case Me!optFilterBy
    'Filter record source dependant on option checked
        Case 1
            strFilterSQL = strSQL & " Where [strShift] = 'Graveyard';"
        Case 2
            strFilterSQL = strSQL & " Where [strShift] = 'Dayshift';"
        Case 3
            strFilterSQL = strSQL & " Where [strShift] = 'Swingshift';"
        
    'If filter applied with no option selected use default record source
        Case Else
            strFilterSQL = strSQL & ";"
    End Select
    
' Set record source with filtered SQL
    Me.RecordSource = strFilterSQL
    Me.Requery

End Sub

Since I could not figure out how to make a calculated field work, as a test I added a “bound” combo box (Value List) with the three shifts. When you select the shift from the combo box the sort works fine, but this is an extra step and a number of times the assistants have selected the wrong shift.

I would like to have a field (that will be hidden) that returns Graves, Days or Swings based on the time in “TimeIn” or the “Book Date”. I can change this to General Date so it displays Date/Time. (I right-click on this field to sort by date after sorting by Shift.) I will also be creating a report that will be printed at the end of each shift that will show who was booked during the shift. If someone could tell me how to proceed I would be so happy. Been stuck here for a while. Thanks

 

[NigelShaw]

Hello mate,

this could work though you will need to look at a year change.

currently, the code below will look at todays date and give you a shift start of today & 23:00. it will then add a day and give you a shift end of today + 1 day & 05:00

you should chand the the line

dteSetGYDateStart = Date

dteSetGYDateStart = forms!yourform!yourcontrol.value to get the date you need on selection but im sure you can work that out.

Private Sub Form_Load()
Dim dteSetGYDateStart As Date
Dim dteSetGYDateEnd As Date
Dim tmeSetGYStart As Date
Dim tmeSetGYEnd As Date
Dim DteFullStrt As Date
Dim DteFullEnd As Date
'set current date or change the "date" to a control on your form as the selected date
dteSetGYDateStart = Date
'format the time to the shift start. in this case, 23:00 hrs
tmeSetGYStart = Format(Now, "23:00")


'you will need to think about the change of year i.e. shift starting at 23:00 31st December 2008 & ending 1st January 2009
'format end of shift adding 1 day to the start date
dteSetGYDateEnd = DateAdd("d", 1, dteSetGYDateStart)
'format the time to the shift end
tmeSetGYEnd = Format(Now, "5:00")
'combine the complete start date / time with the end date / time
DteFullStrt = dteSetGYDateStart & " " & tmeSetGYStart
DteFullEnd = dteSetGYDateEnd & " " & tmeSetGYEnd
'check the values are correct. ( this can be removed afterwards )
MsgBox (DteFullStrt & vbCrLf & DteFullEnd), vbOKOnly, "Graveyard shift"

'you could then have hidden boxes on the form to hold the dtefullstrt & dtefullend data to use a between function for filtering between dates.


End Sub

regs,

nigel

 

[The_Doc_Man]

If you want a string returned that says "Graveyard" or "Swing" or "Day" based on an input date/time, here is one way to do it...

In a general module in your DB, place this code:

Public Function ShiftName( TheTime as Date ) as String

Dim TheHour as Long

TheHour = CLng( DatePart( "h", TheTime ) )

Select Case TheHour

Case 0 to 5
     ShiftName = "Graveyard"
Case 6 to 14
     ShiftName = "Day"
Case 15 to 22
     ShiftName = "Swing"
Case 23
     ShiftName = "Graveyard"
Case Else
     ShiftName = "Off the clock"
End Select
End Function

Play with the numbers in the individual CASE ranges until you get the results you want.

If it is defined as Public in an arbitrary general module in your DB, you can call it in a query or form or whatever. Input a field in DATE format as the only argument. DO NOT input a text field that happens to be date-formatted for display.

 

[poucedeleon]

Record Sort using Time/Date, that crosses midnight

If it is defined as Public in an arbitrary general module in your DB, you can call it in a query or form or whatever. Input a field in DATE format as the only argument. DO NOT input a text field that happens to be date-formatted for display

Nigel,
Can you explain this part of your Code. The one thing I did not put in my post is I am new to VBA (Probably never heard that before) so if you could give me a little more info I don't understand the part about "Input a field in Date format/Do NOT input a text field that happens to be date-formatted for display"
I would appreciate it.

 

[NigelShaw]

Hi,

the section you have quoted is actually from The_Doc_Man not me but i can explain to you anyway.

"If it is defined as Public in an arbitrary general module in your DB, you can call it in a query or form or whatever"

Usually, on the top of the module uner Option Explicit, you would declare variables as public
Public dteMyDate As Date
Public strMyString As String
public intMyNumber As Integer
Public lstMyList As Variant

where the dteMyDate etc would be the name of the variable you are using. reason for this is that it makes it public to the rest of your code rather than limited to your procedure.

"Input a field in DATE format as the only argument. DO NOT input a text field that happens to be date-formatted for display"

In a table, you can choose the field type in the design view. this would determine the data type. on the other hand, you could have a standard textbox on your form that has a field type as text or General. a date in this box would be viewed as text rather than a date whereas a date in a textbox with a date type, would be viewed as a date.

regs,


Nigel

 

[poucedeleon]

Record Sort using Time/Date, that crosses midnight

you could then have hidden boxes on the form to hold the dtefullstrt & dtefullend data to use a between function for filtering between dates.

Nigel thanks for the explaination. You are right I did quote the wrong section. It was your post I wanted to inquire about. I wanted to ask if I should have fields in my table that hold the values for dtefullstrt & dtefullend. It took me a minute to figure out the code.
Since this is just for Graveyard. To handle the other shifts would I need to dimension the other shifts. Should this be done in a public module, and if so. Can you show me an example of how I would call it.
Thanks a lot for the help.