get records

[ndragonx]

Say i want to make two queries for each shift to pull records for orders completed from the time the shift start till their shift ends.
1st shift

select date from table
having orders.complete
where completetime between 5:00 AM and 3:29 PM



2nd shift
select date from table
having orders.complete
where completetime between 3:30 PM and 4:59 AM

How would i do this?

[June7]

The second shift is crossing midnight, this extends the period over two days. This requires the date part in the range, not just time.

How is the time stored - is it in a date/time field? Provide example data. If you want to provide db for analysis, follow instructions at bottom of my post.

[ndragonx]

The second shift is crossing midnight, this extends the period over two days. This requires the date part in the range, not just time.

How is the time stored - is it in a date/time field? Provide example data. If you want to provide db for analysis, follow instructions at bottom of my post.

yes it is saved in a date/time field and it is set when a checkbox is clicked setting order complete to true and then hiding the order
also when clicked the timecomplete field in table is set using now();
doesnt work i get nothing back:
between #9/23/15 5:00:00 AM# and #9/23/15 3:30:00 PM# in criteria

does work:
between #9/23/15 5:00:00 PM# and #9/23/15 10:30:00 PM# in criteria

like you said it only works if i dont go past midnight or use both pm or am in both my constants

what i want to do is grab all orders from like i stated above and cant figure out how to do it.
from what ever date i want to pull from and between their respective shift times.

i may just add a parameter for both my constants for me to enter.

I am new to access and vba so sorry if have trouble explaining.

thanks

[ndragonx]

between #9/23/15 5:00:00 AM# and #9/23/15 3:30:00 PM# in criteria

like you said it only works if i dont go past midnight or use both pm in my and argument

[June7]

Try using 24-hour clock time.

between #9/23/15 05:00:00# and #9/23/15 15:30:00#

Disregarding the date while crossing midnight is not a requirement I've ever seen.

Seems you want to grab all completed records but group them by 2 shift periods. Calculate a shift identifier and group/filter/sort on that value.

ShiftID: IIf(Format([OrderDate], "hh:mm") BETWEEN "05:00" AND "15:29", "Shift1", "Shift2")

[ndragonx]

yeah figured it out, i wasnt changing the date to the next day when passing midnight.
worked after i did that thanks for the help.