Time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
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#
 
Thanks for the help. I appreciate the input. I am assuming that to use this I
need to combine my date and time fields/
 
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#
 
Back
Top