Shift is string field with values like 07:00-15:00. Range criteria will not work. Will have to parse this string to calculate fields that can be referenced in criteria as date/time values.
Shift is string field with values like 07:00-15:00. Range criteria will not work. Will have to parse this string to calculate fields that can be referenced in criteria as date/time values.
Use Date() instead of Now() for txt_Date DefaultValue.
This criteria works:
WHERE (((tblRota.Dt)=[Forms]![frmRota]![txt_Date]) AND ((tblRota.Shift)="07:00-15:00"));
But there are more than 3 shift ranges in table (like "08:00-15:00") so will have to parse. I think start time is all that is needed. The non-time values like Off and Holiday are complicating. Consider:
WHERE ((([Dt]+CDate(Left([Shift],5))) Between [Forms]![frmRota]![txt_Date]+#12/30/1899 07:0:0# And [Forms]![frmRota]![txt_Date]+#12/30/1899 14:59:59#) AND ((Val([Shift]))<>0));
WHERE ((([Dt]+CDate(Left([Shift],5))) Between [Forms]![frmRota]![txt_Date]+#12/30/1899 15:0:0# And [Forms]![frmRota]![txt_Date]+#12/30/1899 22:59:59#) AND ((Val([Shift]))<>0));
WHERE ((([Dt]+CDate(Left([Shift],5))) Between [Forms]![frmRota]![txt_Date]+#12/30/1899 23:0:0# And [Forms]![frmRota]![txt_Date]+1+#12/30/1899 06:59:59#) AND ((Val([Shift]))<>0));
But where do shifts like 20:00-06:00 and 21:00-07:00 belong - Afternoon or Night?
Would be simpler if you assigned a ShiftCode.
Simplified code works:
Code:
Private Sub cmdDateDown_Click()
Me.txt_Date = Me.txt_Date - 1
End Sub
Private Sub cmdDateNow_Click()
Me.txt_Date = Date
End Sub
Private Sub cmdDateUp_Click()
Me.txt_Date = Me.txt_Date + 1
End Sub