Checking whether current time is in range of

[ashm01]

I have 2 fields in short date format(TIMEstart and TIMEend) in an access table, I need to know whether the current time is within the range. It has to return all records if the criteria matches...
something like this

Select * From table where Now> timestart and Now < Timeend

Its going to be pretty simple for majority of you

please help.

Sincerely,

Ashm01

 

[DanChard]

I think the where condition should be:

where format(timestart,'hh:mm') < format(now(),'hh:mm') < format(timeend,'hh:mm')

Hope this is of some use...

 

[ashm01]

well that worked pretty good..but, now another issue arises..For some reason if my timeEnd overlaps 23:59:59 PM into AM, i get no records..any solution for this?

 

[DanChard]

If, say, timestart was 9am (09:00) and timeend was 5:30 pm (17:30) then Access would happily return all times as follows:

where 09:00 < current time < 17:30

If however, the time goes past midnight (eg 2am - 02:00) there is a wraparound effect and it becomes less than the timestart. This is like asking the following of Access:

where 09:00 < current time < 02:00

How can 09:00 possibly be less than the current time if the current time must also be less than 02:00 to satisfy the query?

I hope this is enough to explain what is happening and to point you in the right direction towards solving it. I'm sure there are many solutions to this problem but the correct one will need to be carefully considered and weighed up against others with regard to the situation you are dealing with.

Good luck!

 

[ashm01]

thanks..I think I got a solution.