sub form for between two times

[murray83]

evening all

have an issue with time between criteria as below

 >="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

 

[June7]

Because you are using strings not real time values. Try # in place of quote marks. Is field a date/time type?

 

[murray83]

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]

Time is crossing midnight. Need to include date part.

 

[murray83]

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

 

[June7]

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]

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]

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:

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