how can i show nearest matching record to a current time? 1

[chartpop]

ok, in my database i have StartTime | EndTime | DayDay
start time is in the format 22:30
end time is in the same format i.e 00:30
dayday is the numerical equiv of day of the week i.e sunday = 1, monday = 2, etc.

What i want to display is the nearest matching recording where StartTime >= FormatDateTime(NOW, 4)

I want it to display the record with the nearest start time that has started but not yet ended.

this is the nearest i got...

WHERE StartTime > '" + FormatDateTime(NOW, 4) + "' AND (DayDay = " + Replace(rsSchedule__varDayDay, "'", "''") + ")"

how could i do something like this

WHERE StartTime = FormatDateTime(NOW, 4) OR FormatDateTime(NOW, 4) BETWEEN StartTime AND EndTime

i know the above code is not correct, but it should help you understand what im trying to do.

 

[pkailas]

so you have a Day 6 record that the starttime was 00:03 and EndTime is 06:00 right? What is the day7 reference you make?

that is strange. I'd think if you used <> 6 it could show any record within the time range for days 1-5 and 7

 

[chartpop]

pkailas, thanks for your help.

it seems to work both ways. yours is closer to how I originally planned it in my head, but I must have been getting the parenthesis wrong, so thanks for that tip. interesting that i get the same results without the to AND statements, but I guess as long as it finds the correct entry based on the starttime and its the correct day then there will only be one entry anyway...

my ver

"SELECT TOP 1 * FROM schedule WHERE StartTime <> '" + FormatDateTime(NOW, 4) + "' AND (DayDay = " + Replace(rsSchedule__varDayDayToday, "'", "''") + ")  ORDER BY StartTime DESC"

giving a response

SELECT TOP 1 * FROM schedule WHERE StartTime <> '02:21' AND (DayDay = 7) ORDER BY StartTime DESC

and your ver

"SELECT TOP 1 * FROM schedule WHERE StartTime <= '" & formatdatetime(now, 4) & "' AND EndTime > '" & formatdatetime(now, 4) & "' AND (DayDay = " & Replace(rsSchedule__varDayDayToday, "'", "''") &  ")  ORDER BY StartTime DESC"

gives response

SELECT TOP 1 * FROM schedule WHERE StartTime <= '02:19' AND EndTime > '02:19' AND (DayDay = 7) ORDER BY StartTime DESC

 

[pkailas]

glad I could help

 

[chartpop]

so you have a Day 6 record that the starttime was 00:03 and EndTime is 06:00 right? What is the day7 reference you make?

that is strange. I'd think if you used <> 6 it could show any record within the time range for days 1-5 and 7

your probably right, but I couldnt recreate it.

 

[chartpop]

Hi, just to tie this thread un neatly.

I found that the code was not right, when the start time or end time rolled over onto the next day it was causing the wrong records to be diplayed. Ive got round this by creating a new variable that use IF statements to assertain the numeric value of the 'nextday'.

this has been tested and works, and pkailas I now understand the AND, OR, expressions... you helped that sink in with your first post.

"SELECT TOP 1 * FROM schedule WHERE (StartTime <= '" & formatdatetime(now, 4) & "' AND DayDay = " & varToday &  ") AND (EndTime > '" & formatdatetime(now, 4) & "' AND DayDay = " & varToday &  ") OR (StartTime <= '18:49' AND DayDay=1) AND (EndTime <> '" & formatdatetime(now, 4) & "' AND DayDay = " & nextday &  ") ORDER BY StartTime ASC"

which is effectivley this sql statement.

SELECT TOP 1 * FROM schedule WHERE (StartTime <= '00:16' AND DayDay = 1) AND (EndTime > '00:16' AND DayDay = 1) OR (StartTime <= '18:49' AND DayDay=1) AND (EndTime <> '00:16' AND DayDay = 2) ORDER BY StartTime ASC

I was considering putting in NAND statements but I think its probably overkill.