date based query fails to find records in given period

[danatafl]

Access 2007. I have a query that groups records into a time period: p1 is 1
minute past midnight to 6am, p2 is 6:01am to noon, p3 is 1 minute past noon
to 6pm and p4 is 6:01pm to midnight (SQL below).

For some reason, I get no records for period 3. I have tried all sorts of
combinations (12 and 24 hour clock, dateadd (+ve and -ve variations)) all
without success. If I vary the time between 12:01 and 12:59 I still get
nothing in this period. If I restrict the range to 1:01 pm to 6pm records are
returned (so there are records in the range).

I am missing some silly mistake somewhere or there is something else going on.

Period: IIf([real_entry_time] Between #12:01:00 a.m.# And #6:00:00
a.m.#,"p1_00:01 to 06:00",IIf([real_entry_time] Between #6:01:00 a.m.# And
#12:00:00 p.m.#,"p2_06:01 to 12:00",IIf([real_entry_time] Between #12:01:00
p.m.# And #6:00:00 p.m.#,"p3_12:01 to 18:00","p4_18:01 to 00:00")))

Thank you for your time.

 

[danatafl]

Whoops! Wrong discussion group. Sorry about that.

 

[Diane Poremsky [MVP]]

I think you'll get better responses in the Access newsgroup, not Outlook.

--
Diane Poremsky [MVP - Outlook]



Outlook Tips by email:
(e-mail address removed)

EMO - a weekly newsletter about Outlook and Exchange:
(e-mail address removed)

You can access this newsgroup by visiting
http://www.microsoft.com/office/community/en-us/default.mspx or point your
newsreader to msnews.microsoft.com.

 

[danatafl]

I made a mistake - see previous post from me

I think you'll get better responses in the Access newsgroup, not Outlook.

--
Diane Poremsky [MVP - Outlook]



Outlook Tips by email:
(e-mail address removed)

EMO - a weekly newsletter about Outlook and Exchange:
(e-mail address removed)

You can access this newsgroup by visiting
http://www.microsoft.com/office/community/en-us/default.mspx or point your
newsreader to msnews.microsoft.com.

Access 2007. I have a query that groups records into a time period: p1 is
1
minute past midnight to 6am, p2 is 6:01am to noon, p3 is 1 minute past
noon
to 6pm and p4 is 6:01pm to midnight (SQL below).

For some reason, I get no records for period 3. I have tried all sorts of
combinations (12 and 24 hour clock, dateadd (+ve and -ve variations)) all
without success. If I vary the time between 12:01 and 12:59 I still get
nothing in this period. If I restrict the range to 1:01 pm to 6pm records
are
returned (so there are records in the range).

I am missing some silly mistake somewhere or there is something else going
on.

Period: IIf([real_entry_time] Between #12:01:00 a.m.# And #6:00:00
a.m.#,"p1_00:01 to 06:00",IIf([real_entry_time] Between #6:01:00 a.m.# And
#12:00:00 p.m.#,"p2_06:01 to 12:00",IIf([real_entry_time] Between
#12:01:00
p.m.# And #6:00:00 p.m.#,"p3_12:01 to 18:00","p4_18:01 to 00:00")))

Thank you for your time.