Filter Time
- Thread starter Malkim92
- Start date
Based on your post, I'm not sure what you are really after. Perhaps you can explain a little further or perhaps provide some sample data along with what you would expect as output from the query?
Let's say for example that you want to extract all records that have time values between 6pm and 4am regardless of the date. This query would do that
SELECT tblMyData.*
FROM tblMyData
WHERE (myTIMEField between #06:00:00 PM# and #11:59:59 PM# ) OR (myTIMEField between #12:00:00 AM# and #04:00 AM#)
Let's say for example that you want to extract all records that have time values between 6pm and 4am regardless of the date. This query would do that
SELECT tblMyData.*
FROM tblMyData
WHERE (myTIMEField between #06:00:00 PM# and #11:59:59 PM# ) OR (myTIMEField between #12:00:00 AM# and #04:00 AM#)
Hey,
Basically I have a query that when I open, I need it to show me all the cases entered in that day. Now the shift is between about 6:00PM to 4:00AM. So i want the query to filter all the cases entered on that shift.
Basically I have a query that when I open, I need it to show me all the cases entered in that day. Now the shift is between about 6:00PM to 4:00AM. So i want the query to filter all the cases entered on that shift.
Now, if you run the query after the shift, you need the query to select from 6PM the previous date to 4AM on today's date. That WHERE clause would look like this:
WHERE (mydatefield + myTIMEField BETWEEN dateadd("d",-1,date()) + #06:00:00 PM# AND date() + #04:00 AM#)
WHERE (mydatefield + myTIMEField BETWEEN dateadd("d",-1,date()) + #06:00:00 PM# AND date() + #04:00 AM#)
Hey, Sorry i couldnt reply but Ive been a bit busy lately.
Well, I am a bit new to Access so where do i put that? I have a table called "MainTable" and the 2 fields are "Day" and "Time". Now do i go to SQL view of the query and enter:
SELECT Time, Day
FROM MainTable
WHERE (Day + Time BETWEEN dateadd("d",-1,date()) + #06:00:00 PM# AND date() + #04:00 AM#
Again, I'm new to access so i need much help.
Thanks for all your help.
Well, I am a bit new to Access so where do i put that? I have a table called "MainTable" and the 2 fields are "Day" and "Time". Now do i go to SQL view of the query and enter:
SELECT Time, Day
FROM MainTable
WHERE (Day + Time BETWEEN dateadd("d",-1,date()) + #06:00:00 PM# AND date() + #04:00 AM#
Again, I'm new to access so i need much help.
Thanks for all your help.
You would create a query. You can start with the design grid view & put in your tables and select the fields you want. You could create a calculated field in the query day+time and in it's criteria section put: Day + Time BETWEEN dateadd("d",-1,date()) + #06:00:00 PM# AND date() + #04:00 AM#
Alternatively, you could just copy and paste the entire text in the SQL view of the query
SELECT Time, Day
FROM MainTable
WHERE Day + Time BETWEEN dateadd("d",-1,date()) + #06:00:00 PM# AND date() + #04:00 AM#
BTW, the words day and time are reserved words in Access, so I would strongly recommend changing those field names to some other name. You will have to adjust the SQL text accordingly.
Alternatively, you could just copy and paste the entire text in the SQL view of the query
SELECT Time, Day
FROM MainTable
WHERE Day + Time BETWEEN dateadd("d",-1,date()) + #06:00:00 PM# AND date() + #04:00 AM#
BTW, the words day and time are reserved words in Access, so I would strongly recommend changing those field names to some other name. You will have to adjust the SQL text accordingly.
.