find periods of time in a longer period

[ofird]

Hi,

Let me explain (I have been trying to figure this out for days):

1) I have a wide range of time, lets say one year. During this year many orders have been taken.

2) I would like to find all the orders which have been taken from monday 15:00 untill tuesday 17:00 (for instance).

3) What I have done is to calculate the start minute number of the starting time and the end minute number of the ending time (the calculation is from the start of the week).

4) Now I am looking for all orders which have been taken in that range.

5) To do this I call a function from the query on the orders date which will convert it to number and I am trying to use the criteria "between startMinute and endMinute".

The problem is that I keep on getting the warning that the
"expression is too complicated...", when i remove the criteria I am getting the minute number from the function I used.

How can this issue be resolved?

Thank you

 

[r937]

function? calculate minutes? yeah, that does sound complicated ;-)

try this --

[tt]select orderno, orderamt, orderdate
from orders
where ( datepart("w",orderdate) = 2
and hour(orderdate) >= 15 )
or ( datepart("w",orderdate) = 3
and hour(orderdate) <= 17 )[/tt]

if the range is from, say, monday at 15 to thursday at 17, then it would be

[tt] where ( datepart(&quot;w&quot;,orderdate) = 2
and hour(orderdate) >= 15 )
or ( datepart(&quot;w&quot;,orderdate) = 3 )
or ( datepart(&quot;w&quot;,orderdate) = 4 )
or ( datepart(&quot;w&quot;,orderdate) = 5
and hour(orderdate) <= 17 )[/tt]

neat, eh?


rudy

 

[ofird]

Thanks for this post rudy,
I thought about it, but I ewas worried, that I couldn't calculate it per minutes (not hour), but I think I can (by using &quot;n&quot; instead of &quot;h&quot.
Why didn't you use &quot;Between&quot; instead of all the &quot;Or&quot;s, this kind of usage will have to be calculated on my form and will cost me some time and make the query unclear.

Do you think I can calculate the start minute and the end minute and find in between them, like:

where (datepart (&quot;n&quot;,orderdate) > [forms]![itemsales][startMinute] and datepart (&quot;n&quot;,orderdate) < [forms]![itemsales][endMinute])

?
Ofir
?

 

[r937]

yes, i'm sure you can calculate it, i wasn't aware that you wanted it to be a form input

to use BETWEEN, you could construct a datetime value from the staring date and time, and another datetime value from the ending date and time

i don't do a lot of access forms (like: none) so i'm not sure how you would input a date and time value and construct a datetime

maybe the DateSerial function


rudy