Time

S

sacredarms

I need to query a database where I need all the transactions from start time
22:00 and end time is 02:00 I have a parameter query set where date is from
"x" - "y" and one for Where time is. I seem to be not getting accurate
answers. If I query the table twice, once from 22:00 - 23:59 then from 00:00
- 02:00 then I get the correct answer although it is not the most direct
route. Any help in setting up the parameter that might make it work once
would be appreciated.

Thank you
 
M

Michel Walsh

Hi,


Keep the date with the time:

BETWEEN #10/28/2004 22:00:00# AND #10/29/2004 00:02:00#



Alternatively, if you have ONLY the time part in:

BETWEEN time1 AND time2


where time2 can be past midnight, then try:

BETWEEN time1 AND time2 + iif(time2<time1, 1 , 0)




Hoping it may help,
Vanderghast, Access MVP
 
M

Michel Walsh

Hi,


The second part won't work as announced. You have to have the date + time
stored in the database, and you supply the date (and time) for time1 but
only the time, in time2 (quite complex set of conditions after all), then:

BETWEEN time1 AND DateValue(time1)+time2 + iif(time2<TimeValue(time1), 1 ,
0)



If you have only time in the database ( and in the parameters ), you have
to proceed as you did:

WHERE timeField BETWEEN time1 AND iif( time2>time1, time2, #24:00:00#)
OR
timeField BETWEEN iif(time2<time1, 0, time1) AND time2



Hoping it may help,
Vanderghast, Access MVP
 
S

sacredarms

It seems to have me stumped even with the help. I guess I am still to new to
make this work. I am really trying to find a simple way of returning only
transactions between 01/01/02 through 01/01/04 between 21:00 - 01:00. My date
is in a field called ring_date and the time is in a field called time.
Thanks again
 
M

Michel Walsh

Hi,


Have you tried to supply the date and the time:


BETWEEN #10/28/2004 21:00# AND #10/29/2004 01:00#


you should have the date and time in one single field, that makes
comparisons easier. In your case, having them in two different fields, you
have to add the fields:


(FieldWIthDateOnly + FiledWithTimeOnly) BETWEEN ....




If you want an event that occurs ANY DATE, but between the two time
value The SQL statement (in SQL view) may look like:


....
WHERE
EventWithTimeOnly BETWEEN time1 AND iif( time2>time1, time2,
#24:00:00#)
OR
EventWithTimeOnly BETWEEN iif( time2>time1, time1, #00:00:00#) AND
time2



and time1 and time2 being the two arguments. DateValue( ) and TimeValue( )
can be used to get the date part only and the time part only of a date_time
value.




Hoping it may help,
Vanderghast, Access MVP
 
J

John Spencer (MVP)

Try the following or some variation of it.

Where DateDiff("n",-180,YourDateField) Between X and Y
AND DateDiff("n",-180,YourTimeField) Between #19:00:00# and #23:00:00#
 
S

sacredarms

Thanks for the help. I appreciate the input. I am assuming that to use this I
need to combine my date and time fields/
 
J

John Spencer (MVP)

Let's go over the specifications first.

What I assumed you want is to get records where the time is between two specific
times on a range of dates. That is, Get everything that happens between 5 and 6
PM on any of the dates between Oct 12 and Oct 15. If the time is not between
22:00 and 02:00 then I don't want to see that record.

If that is the case, try my suggestion. I'm not sure it will work, but I think
it will.

Yes, you are probably correct that you will need to add your date field to your
time field for the first part of the criteria. I did miss that when I made the
suggestion. Also, I used DateDiff when I meant to use DateAdd. Sorry for the confusion.

Where DateAdd("n",-180,CDate(YourDateField + YourTimeField)) Between X and Y
AND DateAdd("n",-180,YourTimeField) Between #19:00:00# and #23:00:00#
 
Top