sub form for between two times (1 Viewer)

murray83

Games Collector
Local time
Today, 10:01
Joined
Mar 31, 2017
Messages
728
evening all

have an issue with time between criteria as below

Code:
 >="23:00" And <="07:00"

this is for the 3rd subofrm on my form and i have data it should show but all i get is a big fat nothing
 

Attachments

  • FORM.png
    FORM.png
    25.7 KB · Views: 92
  • SOME DATA.png
    SOME DATA.png
    8.2 KB · Views: 82
Last edited:

June7

AWF VIP
Local time
Today, 02:01
Joined
Mar 9, 2014
Messages
5,423
Because you are using strings not real time values. Try # in place of quote marks. Is field a date/time type?
 

murray83

Games Collector
Local time
Today, 10:01
Joined
Mar 31, 2017
Messages
728
Because you are using strings not real time values. Try # in place of quote marks. Is field a date/time type?


Yes the field is date/time and tried the # in place of quotes still no dice, or data even
 

June7

AWF VIP
Local time
Today, 02:01
Joined
Mar 9, 2014
Messages
5,423
Time is crossing midnight. Need to include date part.
 

murray83

Games Collector
Local time
Today, 10:01
Joined
Mar 31, 2017
Messages
728
Time is crossing midnight. Need to include date part.

ok but how do i do that ??

the form with the subforms in question is frmRota
 

Attachments

  • Stock People Tracker - Copy.accdb
    1.4 MB · Views: 82

June7

AWF VIP
Local time
Today, 02:01
Joined
Mar 9, 2014
Messages
5,423
Instead of Int(Now()) just use Date().

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.
 

murray83

Games Collector
Local time
Today, 10:01
Joined
Mar 31, 2017
Messages
728
Instead of Int(Now()) just use Date().

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.

ok not sure how to parse the string, so how if i split the field into a start shift time and end shift time ?
 

June7

AWF VIP
Local time
Today, 02:01
Joined
Mar 9, 2014
Messages
5,423
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
 
Last edited:

Users who are viewing this thread

Top Bottom