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.

 

[Allen Browne]

Is there any chance that the records in your table contain a date as well as
a time? (The date part may not show up if the Format is Short Time or
similar.)

If that is not the issue, declare the parameter.
Choose Parameters on the Query menu in query design view.
Access opens a dialog.
Enter:
[real_entry_time] Date

(This assumes the field you placed this expression under is a Date/Time
field, not a Text field.)

 

[Gina Whipp]

Try changing a.m to AM and p.m. to PM...

 

[danatafl]

I'm sorry, I don't understand how this would help. real_entery_time is a
field on the table not a parameter. The field holds a time only (populated
from a date/time field using format function and hh:nn format option). I
tried anyway but all that happened was that I was prompted for the new
parameter value and was still missing period three records.

Is there any chance that the records in your table contain a date as well as
a time? (The date part may not show up if the Format is Short Time or
similar.)

If that is not the issue, declare the parameter.
Choose Parameters on the Query menu in query design view.
Access opens a dialog.
Enter:
[real_entry_time] Date

(This assumes the field you placed this expression under is a Date/Time
field, not a Text field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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]

I tried this but as soon as I exit the qbe field, the format reverts to
"a.m." or "p.m."

Try changing a.m to AM and p.m. to PM...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

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.

 

[John W. Vinson]

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.

I'd try the Switch() function instead:

Switch([real_entry_time] < #06:00#, "p1_00:01 to 6:00",
[real_entry_time] < #12:00#, "p2_06:01 to 12:00",
[real_entry_time] < #18:00#, "p3_12:01 to 18:00",'
True, "p4_18:01 to 00:00")

The Switch function takes arguments in pairs, returning the second of the pair
when it first finds a True value in the first.

If [real_entry_time] actually contains a date and time, use

TimeValue([real_entry_time])

in each case.

Note that a Date/TIme value is stored as a double float count of days and
fractions of a day since midnight, December 30, 1899. Therefore #06:00# is
actually stored as 0.25 and is actually #12/30/1899 06:00#.

 

[Gina Whipp]

Period: IIf([real_entry_time] Between #12:01:00 AM# And #06:00:00
AM#,"p1_00:01 to 06:00",IIf([real_entry_time] Between #06:01:00 AM# And
#12:00:00 PM#,"p2_06:01 to 12:00",IIf([real_entry_time] Between #12:01:00
PM# And #06:00:00 PM#,"p3_12:01 to 18:00","p4_18:01 to 00:00")))

I got the above to work for those times you specified. Are you sure it's a
Date/Time field you are querying on? Of course, I can't get anything to
work if I use a dot m dot, I have to remove the dots.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

I tried this but as soon as I exit the qbe field, the format reverts to
"a.m." or "p.m."

Try changing a.m to AM and p.m. to PM...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

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.

 

[Allen Browne]

Sorry: I did misunderstand your example.

Try:
Hour([real_entry_time]) \ 6

That should yield a number between 0 and 3.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm sorry, I don't understand how this would help. real_entery_time is a
field on the table not a parameter. The field holds a time only (populated
from a date/time field using format function and hh:nn format option). I
tried anyway but all that happened was that I was prompted for the new
parameter value and was still missing period three records.

Is there any chance that the records in your table contain a date as well
as
a time? (The date part may not show up if the Format is Short Time or
similar.)

If that is not the issue, declare the parameter.
Choose Parameters on the Query menu in query design view.
Access opens a dialog.
Enter:
[real_entry_time] Date

(This assumes the field you placed this expression under is a Date/Time
field, not a Text field.)

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")))

 

[danatafl]

Timevalue worked so I guess the data was not being recognised appropriately
for some reason. Thank you also for introducing me to switch.

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.

I'd try the Switch() function instead:

Switch([real_entry_time] < #06:00#, "p1_00:01 to 6:00",
[real_entry_time] < #12:00#, "p2_06:01 to 12:00",
[real_entry_time] < #18:00#, "p3_12:01 to 18:00",'
True, "p4_18:01 to 00:00")

The Switch function takes arguments in pairs, returning the second of the pair
when it first finds a True value in the first.

If [real_entry_time] actually contains a date and time, use

TimeValue([real_entry_time])

in each case.

Note that a Date/TIme value is stored as a double float count of days and
fractions of a day since midnight, December 30, 1899. Therefore #06:00# is
actually stored as 0.25 and is actually #12/30/1899 06:00#.