Query to identify if a time period is between times that pass through midnight? (3 Viewers)

[keeper]

Yes I changed the query date format to match mm/dd/yyyy hh:nn

Still all fail

Checking the code it refers to ChangeStart, in_ChangeEnd however the DB has
Change_Start & Change_End which I can easily change.

Should both these lines refer to ChangeStart??
dt_MustStartBy = CDate(DatePart("m", in_ChangeStart) & "/" & DatePart("d", in_ChangeStart) & "/" & DatePart("yyyy", in_ChangeStart) & " " & in_DayStart)
dt_MustEndBy = CDate(DatePart("m", in_ChangeStart) & "/" & DatePart("d", in_ChangeStart) & "/" & DatePart("yyyy", in_ChangeStart) & " " & in_DayEnd)
' sets specific required start and end date/times

Many Thanks

 

[plog]

Yes they should. The next line in that code moves dt_MustEndBy to the next day in case the timespan goes over midnight, so that is correct.

Can you post back the database you have?

 

[keeper]

here you go...

 

[plog]

Here's a screenshot of my results from your database:

 

[keeper]

Strange , date format issue somewhere?
Although I set the query to mm/dd/yyyy hh:nn to match yours.

 

[keeper]

Ok, now I understand, I changed the code to -
dt_MustStartBy = CDate(DatePart("d", in_ChangeStart) & "/" & DatePart("m", in_ChangeStart) & "/" & DatePart("yyyy", in_ChangeStart) & " " & in_DayStart)
dt_MustEndBy = CDate(DatePart("d", in_ChangeStart) & "/" & DatePart("m", in_ChangeStart) & "/" & DatePart("yyyy", in_ChangeStart) & " " & in_DayEnd)

This now matches that date format in the table as dd/mm/yyyy hh:nn.

Our results now match, many thanks for you help......

 

[plog]

Good, glad you got it. Date formats can be a pain.