Stuck on creating a Date/Time Range????

[pmullan]

Hello Everyone, I have a situation where I am needing to create a couple of Constants to use in my code. I need to create a "Dayshift" and a "Nightshift" constant (one each).

Dayshift = Current Date from 6:00 am until current date at 5:59 pm.

Nighshift = Current Date from 6:00 pm until the next day at 5:59 am.

I am relatively new to Access 2013 & VBA and I am having no luck scouring the web for tips / hints as to how to accomplish this.
My shift db that I am creating, works up until Midnight. When the next day rolls around, my current code will break.
I am using a table called "tblshift" that will contain only two entries per day. 1 for dayshift and 1 for nightshift. The current code does comparisons using the date & time. If within the range for Day or Night, it will execute it section. I know I will have a problem between 12:00 am and 5:59 am because the date will increment by 1. I am trying to simplify by implementing the two Constants "Dayshift" & "Nightshift".
I hope this info helps someone to provide me with some help. Thank you, Patrick:banghead:

 

[Gasman]

You have the current date called Date.
Set DayshiftEnd as that date plus 5:59
Subtract 11:59 from that to get DayshiftStart

Do similar for Nightshift.

Then compare your date and times as between start and end for each shift.

So they would not be constants, but global variables that you can use from anywhere when you need to, and also depends on when you run the code

That is how I would approach it, and then tweak as need be.

HTH

 

[pmullan]

Hi Gasman, My apologies, but you have me confused as to what you are saying. I attached the Form load module to this post with the code. Please review it.

When the form loads, it does a check back to the tblShift table to see if any records exist (using dcount with the criteria being the ShiftDate field). From 6am until 5:59pm (dayshift) it works perfectly. From 6pm until midnight, the code (the dcount process) will work.

However, after midnight, when dcount runs against the table, the count will come back as "0" because of the new date. This will then cause the code to not work as it should (will allow more than 2 records for each day)

I need to compensate for the date change when the clock passes midnight. Thats why I thought of using a "Dayshift" and "Nightshift" constants and then reworking the code somehow.

Thanks for the quick reply Gasman and if others can provide help too, I would really appreciate it. Patrick

 

[MarkK]

My shift db that I am creating, works up until Midnight. When the next day rolls around, my current code will break.

Can you show this code? How does this work?

I am using a table called "tblshift" that will contain only two entries per day. 1 for dayshift and 1 for nightshift.

This doesn't make sense to me to do. A DateTime value should be a dimension of some other object, not a whole row in a table. A person has a birth date, a transaction has a date, an order, a hiring, a firing, a sale, all events occur in time. As such, time should be a field in a table; a single dimension of a more complex object.

The concept that you need to populate a table with arbitrarily divided named time intervals can't be right. How do you use this table? How does it relate to your actual data?

Curiously,
Mark

 

[pmullan]

Markk, Hi, the table I am using is called tblShift. There are 6 columns titled:
ShiftDay, ShiftDate, ShiftTime, Shift, TechIIEmp, TechIIEmp.

Since each shift is 12 hours, there should be 2 records per day. I am basing my code on the ShiftDate column using the start and end times of each shift along with the date in order to determine whether to add a record or not.

My problem is that after midnight, the date changes and thus when "dcount" is run, the count zero's out. This will mess up my code and what I am trying to accomplish.
Thanks, Patrick

 

[MarkK]

Hi,
And what does that table store? Are those shift start and end times for the employee with ID TechIIEmp? If so, you don't need ShiftDay and Shift in there too. That data is said to "vary directly" with the raw date/time data. You can calculate those, so there is no need to store them.

See what I'm getting at? Or have I misunderstood your purpose?
Cheers,
Mark

 

[pmullan]

Mark, This table holds the following information;
Day, Date & Time that the tech signs in. There are two techs per shift so there are two combo boxes, one for each tech and then there is a combo box for the shift.

When the form loads, the day, date & time are preloaded. Then one of the techs will select which shift they are working (dayshift / nightshift) and finally select their names (techs working the shift). After this, then they can proceed to select what tasks they need to do. If they close the DB, then when they restart, it will check to see if they already signed in. If so, the data is retrieved and populated. If it is after 6pm, and the next shift is coming onduty, when it loads, it checks the time and also how many records exist for the day. If only the dayshift exist, the program will let the users sign in. If they close the DB later and then restart it, it will check the table for the number of records for the day (Shiftdate). If a record exist for both the dayshift and nightshift, then the data is retrieved from the table and loaded into the form.
My issue is that during the nightshift, the Date will advance 1 day and when the form loads, "dcount" will no longer see the 2 records since it is a new day (until 6am when dcount should actually see no records). I hope this explains what I am trying to do.
Thank you, Patrick

 

[arnelgp]

you are making things hard, when
it would be easy.

you need only revised your table
and add a couple of field:

remove ShiftDate, ShiftTime

replaced with:

ShiftDateIn Date/Time (format Date + Time)
ShiftDateOut Date/Time (format Date + Time)

sample record:

record 1:
ShiftDateIn: 09/30/2017 6:00 AM
ShiftDateOut: 09/30/2017 5:59 PM

record 2:
ShiftDateIn: 09/30/2017 6:00 PM
ShiftDateOut: 10/01/2017 5:59 AM '<= advance the date by 1

then you dont need the field for "NightShift/DayShift"
coz its obvious on their schedule.
also this will guarantee that your query
will work for the two shifts.
no need for complex calculation, ie:
add one day, subract another time, etc.

to know how many is in shift for at
the Time when the query was executed:

dcount("*","shifttable","Now() Between ShiftDateIn And ShiftDateOut;")

or to be specific tech at that time:

dcount("*","shifttable","id=" & techid & " And Now() Between ShiftDateIn And ShiftDateOut;")

 

[Gasman]

arnelgp has explained it better. Forget about dates on their own, use the date and time as one value as Arnelgp has shown.

Hi Gasman, My apologies, but you have me confused as to what you are saying. I attached the Form load module to this post with the code. Please review it.

When the form loads, it does a check back to the tblShift table to see if any records exist (using dcount with the criteria being the ShiftDate field). From 6am until 5:59pm (dayshift) it works perfectly. From 6pm until midnight, the code (the dcount process) will work.

However, after midnight, when dcount runs against the table, the count will come back as "0" because of the new date. This will then cause the code to not work as it should (will allow more than 2 records for each day)

I need to compensate for the date change when the clock passes midnight. Thats why I thought of using a "Dayshift" and "Nightshift" constants and then reworking the code somehow.

Thanks for the quick reply Gasman and if others can provide help too, I would really appreciate it. Patrick

 

[arnelgp]

since you don't want to change your
table structure, you might
as well test this

Private Sub Form_Load()
    
 Dim dbs As DAO.Database
 Dim rst As DAO.Recordset
 Dim strCriteria As String
 Dim lRecCount As Long
 Dim myDate As Date
 Dim thisDate As Date
      
 'Get the database and Recordset
 Set dbs = CurrentDb
 Set rst = dbs.OpenRecordset("tblShift", dbOpenDynaset)
        
 ' Populate the strCriteria string variable
 strCriteria = "[ShiftDate] = #" & Format(VBA.Date, "mm/dd/yyyy") & "#"
   
 ' Sets up the myDate variable with the current time for use later in this module.
 myDate = Now

If myDate > (Date()-1) + #6:00 PM# And myDate < Date() + #5:59 AM# Then 
    thisDate = Date()-1
Else
    thisDate = Date
End if
 ' This section checks the Shift Table to see if a record exist for the current day.  If a
 ' record does not exist, then the Main Form will popluate the Day, Date & Time fields with
 ' the current date/time info and allow the user to enter their shift information.
 If (DCount("*", "tblShift", "[ShiftDate]=#" & Format(ThisDate,"mm/dd/yyyy") & "#")) =0
     Me.txtShiftDay.Value = Format(Now, "dddd")
     Me.txtShiftDate.Value = Format(Now, "mm/dd/yyyy")
     Me.txtShiftTime.Value = Format(Now, "hh:mm am/pm")
       
     ' Load the combo box title "Shift" with the necessary data and then set the focus to
     ' the "Shift" field.
     Me.cboShift.AddItem ("Dayshift")
     Me.cboShift.AddItem ("Nightshift")
     Me.cboShift.SetFocus
     GoTo Cleanup
 ' Checks to see if a Nightshift Record exist and if so, the Main Form field are populated
 ' with the information from the table.
 ElseIf myDate > (Date()-1) + #6:00 PM# And myDate < Date() + #5:59 AM# Then
     rst.MoveFirst
     rst.FindFirst "[ShiftDate] = #" & Format(Date, "mm/dd/yyyy") & "# And " & _
                   "[ShiftTime] = #6:00 PM#"
     If Not rst.NoMatch Then
             Me![txtShiftDay].Value = rst![ShiftDay]
             Me![txtShiftDate].Value = rst![ShiftDate]
             Me![txtShiftTime].Value = rst![ShiftTime]
             Me![cboShift].Value = rst![Shift]
             Me![cboTechnicianII].Value = rst![TechIIEmp]
             Me![cboTechnicianI].Value = rst![TechIEmp]
             Me.cmdClose.SetFocus
     End If
Else
    ' We are at TheyShift
     rst.MoveFirst
     rst.FindFirst "[ShiftDate] = #" & Format(Date, "mm/dd/yyyy") & "# And " & _
                   "[ShiftTime] = #6:00 AM#"
     If Not rst.NoMatch Then
             Me![txtShiftDay].Value = rst![ShiftDay]
             Me![txtShiftDate].Value = rst![ShiftDate]
             Me![txtShiftTime].Value = rst![ShiftTime]
             Me![cboShift].Value = rst![Shift]
             Me![cboTechnicianII].Value = rst![TechIIEmp]
             Me![cboTechnicianI].Value = rst![TechIEmp]
             Me.cmdClose.SetFocus
     End If

End If
     
Cleanup:
     ' Clean up the DAO
     rst.Close
     Set rst = Nothing
     Set dbs = Nothing
      
End Sub

 

[pmullan]

Arnelgp, Thank you for your advise and the code you provided. I will give it a try and let everyone know how it works out. Patrick

 

[arnelgp]

some refinement.
is your Form bound to tblShift?
if so Uncomment the:

DoCmd.GoToRecord

Private Sub Form_Load()
 Dim dbs As DAO.Database
 Dim rst As DAO.Recordset
 Dim strCriteria As String
 Dim lRecCount As Long
 Dim myDate As Date
 Dim thisDate As Date
      
 'Get the database and Recordset
 Set dbs = CurrentDb
 Set rst = dbs.OpenRecordset("tblShift", dbOpenDynaset)
   
 ' Sets up the myDate variable with the current time for use later in this module.
 myDate = Now

If myDate > (Date - 1) + #6:00:00 PM# And myDate < Date + #5:59:00 AM# Then
    thisDate = Date - 1
    strCriteria = "[ShiftDate]=#" & Format(thisDate, "mm/dd/yyyy") & "# And " & _
            "[ShiftTime] =#6:00:00 PM#"
Else
    thisDate = Date
    strCriteria = "[ShiftDate]=#" & Format(thisDate, "mm/dd/yyyy") & "# And " & _
            "[ShiftTime] =#6:00:00 AM#"

End If
 
 ' This section checks the Shift Table to see if a record exist for the current day.  If a
 ' record does not exist, then the Main Form will popluate the Day, Date & Time fields with
 ' the current date/time info and allow the user to enter their shift information.
 If (DCount("*", "tblShift", strCriteria)) = 0 Then
     
     '*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!
     '* UNCOMMENT this line if your form is bound the tblShift
     '*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!*!
     'DoCmd.GoToRecord , , acNewRec
     
     Me.txtShiftDay.Value = Format(thisDate, "dddd")
     Me.txtShiftDate.Value = Format(thisDate, "mm/dd/yyyy")
     Me.txtShiftTime.Value = IIf(thisDate <> myDate, #6:00:00 PM#, #6:00:00 AM#)
       
     ' Load the combo box title "Shift" with the necessary data and then set the focus to
     ' the "Shift" field.
     Me.cboShift.AddItem ("Dayshift")
     Me.cboShift.AddItem ("Nightshift")
     
     Me.cboShift = IIf(thisDate <> myDate, "Nightshift", "Dayshift")
     
     Me.cboShift.SetFocus
     GoTo Cleanup
 ' Checks to see if a Nightshift Record exist and if so, the Main Form field are populated
 ' with the information from the table.
 ElseIf myDate > (Date - 1) + #6:00:00 PM# And myDate < Date + #5:59:00 AM# Then
     rst.MoveFirst
     rst.FindFirst "[ShiftDate] = #" & Format(thisDate, "mm/dd/yyyy") & "# And " & _
                   "[ShiftTime] = #6:00:00 PM#"
     If Not rst.NoMatch Then
             Me![txtShiftDay].Value = rst![ShiftDay]
             Me![txtShiftDate].Value = rst![ShiftDate]
             Me![txtShiftTime].Value = rst![ShiftTime]
             Me![cboShift].Value = rst![Shift]
             Me![cboTechnicianII].Value = rst![TechIIEmp]
             Me![cboTechnicianI].Value = rst![TechIEmp]
             Me.cmdClose.SetFocus
     End If
Else
    ' We are at TheyShift
     rst.MoveFirst
     rst.FindFirst "[ShiftDate] = #" & Format(thisDate, "mm/dd/yyyy") & "# And " & _
                   "[ShiftTime] = #6:00:00 AM#"
     If Not rst.NoMatch Then
             Me![txtShiftDay].Value = rst![ShiftDay]
             Me![txtShiftDate].Value = rst![ShiftDate]
             Me![txtShiftTime].Value = rst![ShiftTime]
             Me![cboShift].Value = rst![Shift]
             Me![cboTechnicianII].Value = rst![TechIIEmp] & ""
             Me![cboTechnicianI].Value = rst![TechIEmp] & ""
             Me.cmdClose.SetFocus
     End If

End If
     
Cleanup:
     ' Clean up the DAO
     rst.Close
     Set rst = Nothing
     Set dbs = Nothing
      
End Sub

 

[pmullan]

Hey Arnelgp, I want to thank you for all your help. I used your code you posted earlier along with some modifications that I made and I now have the Form Load and Form Close modules working. I will look at the new code you send and see if I can put any of it into action. Thanks again.
Here is the Form Load module so you can see what I did.

Private Sub Form_Load()

' Declare the necessary variables.
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim myDate As Date
Dim newDate As Date

'Get the database and Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblShift", dbOpenDynaset)

' Sets up the myDate variable with the current time for use later in this module.
myDate = Now

' Check to see what the Date is. If after midnight, code will adjust the date
' so that the code will work properly.
If myDate > (Date - 1) + #6:00:00 PM# And myDate < Date + #5:59:00 AM# Then
newDate = (Date - 1)
Else
newDate = Date
End If

' No records exist in the Shift Table. Therefore, go to the AddRecordToForm section.
If (DCount("*", "tblShift", "[ShiftDate]=#" & Format(newDate, "mm/dd/yyyy") & "#")) = 0 Then
GoTo AddRecordToForm
End If

' Check to see if a record exist in the Shift Table for the Dayshift. If one is there,
' then the info is retrieved and the form is populated.
If myDate > (Date - 1) + #6:00:00 PM# And myDate < Date + #5:59:00 PM# Then
rst.MoveFirst
rst.FindFirst "[ShiftDate] = #" & Format(Date, "mm/dd/yyyy") & "# And " & _
"[ShiftTime] > #6:00 AM#"
If Not rst.NoMatch Then
GoTo PopulateForm
End If
End If

' Check to see if a record exist in the Shift Table for the Nightshift. If the record
' exist, then the info is retrieved and the form is populated. If no record exist,
' then go to the AddRecordToForm section.
If myDate > (Date - 1) + #6:00:00 PM# And myDate < (Date + 1) + #5:59:00 AM# Then
rst.MoveFirst
rst.FindFirst "[ShiftDate] = #" & Format(newDate, "mm/dd/yyyy") & "# And " & _
"[ShiftTime] > #6:00 PM#"
If rst.NoMatch Then
GoTo AddRecordToForm
Else
GoTo PopulateForm
End If
End If

AddRecordToForm:
' A record does not exist in the Shift Table. Main Form will popluate the Day, Date & Time fields with
' the current date/time info and allow the user to enter their shift information.
Me.txtShiftDay.Value = Format(Now, "dddd")
Me.txtShiftDate.Value = Format(Now, "mm/dd/yyyy")
Me.txtShiftTime.Value = Format(Now, "hh:mm am/pm")

' Load the combo box title "Shift" with the necessary data and then set the focus to
' the "Shift" field.
Me.cboShift.AddItem ("Dayshift")
Me.cboShift.AddItem ("Nightshift")
Me.cboShift.SetFocus
GoTo Cleanup

PopulateForm:
' Retrieve the data from the Shift table (tblShift) and populate this information
' to the appropriate fields on the form.
Me![txtShiftDay].Value = rst![ShiftDay]
Me![txtShiftDate].Value = rst![ShiftDate]
Me![txtShiftTime].Value = rst![ShiftTime]
Me![cboShift].Value = rst![Shift]
Me![cboTechnicianII].Value = rst![TechIIEmp]
Me![cboTechnicianI].Value = rst![TechIEmp]
Me.cmdClose.SetFocus

Cleanup:
' Clean up the DAO
rst.Close
Set rst = Nothing
Set dbs = Nothing

End Sub