Between Dates and Times

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

Guest

Hi All,

I need to run a query that finds all the records between 7/1/05 and 12/30/05
and only the records between 8:00am and 9:00am. I have the date range part
but I can not figure out the time range part.
Any help would be appreciated Thanks!! Bill
 
Bill said:
Hi All,

I need to run a query that finds all the records between 7/1/05 and
12/30/05
and only the records between 8:00am and 9:00am. I have the date range part
but I can not figure out the time range part.
Any help would be appreciated Thanks!! Bill
Click to expand...

I'd create another field that has the same long date in it as your
original field and add an AND criterion to that just based on the time.

Name: Date1
Criteria: Between #7/1/2005# and #12/30/2005#

Name: Date1a
Criteria: Between #8:00am# and #9:00am#

Tom Lake
 
Bill said:
Hi All,

I need to run a query that finds all the records between 7/1/05 and
12/30/05 and only the records between 8:00am and 9:00am. I have the
date range part but I can not figure out the time range part.
Any help would be appreciated Thanks!! Bill
Click to expand...

No idexed ay of doing this that I know of. Best I can think of is...

SELECT...
FROM TableName
WHERE DateField >= #2005-07-01#
AND DateField < #2005-12-31#
AND TimeValue(DateField) >= #8:00:00#
AND TimeValue(DateField) < #9:00:01#

The above should be able to use an index for the date criteria, but will have to
scan those rows for the Time criteria.

Since "between 8 and 9 am" is subject to different interpretation I guessed on
what you meant by that. The above would include a record whose time was exactly
9:00:00 am but filter out anything later than that.
 
Bill

I can't tell if you are using an Access date/time field to hold your
date/time value, or something else.

An Access date/time field holds a floating point number. The "whole number"
portion (left of the decimal place) is the number of days since a particular
date in the late 1800s (you'll probably find this via Access HELP, if you're
curious). The "decimal fraction" portion (right of the decimal place) is
the decimal portion of 24 hours -- e.g., xxxxx.50000 means noontime.

One way to find out the time would be to subtract the whole number portion
and convert the decimal fraction to hours:minutes:seconds.

If you are only interested in seeing (i.e., displaying) the time portion,
you can use the Format() function to only show the hh:nn:ss portion.
 
Bill said:
I need to run a query that finds all the records between 7/1/05 and 12/30/05
and only the records between 8:00am and 9:00am. I have the date range part
but I can not figure out the time range part.
Click to expand...


Presumably, you are applying the criteria to a date/time
field. If so, then use a criteria like this:

DateValue(datetime) Between #7/1/05# And #12/31/05# AND
TimeValue(datetime) Between #8:00# And #9:00#
 
Thanks to All for the quick reply!! I was able to use Rick & Marshall's
suggestion and ended up with the following statment:

SELECT Incident.*
FROM Incident
WHERE (((Incident.time_open)>=#7/1/2005# And
(Incident.time_open)<#12/31/2005#) AND ((TimeValue([time_open]))>=#12/30/1899
8:0:0# And (TimeValue([time_open]))<#12/30/1899 9:0:1#));

Works like a charm!!

Thansk again to all!!
 
One nitpick. Your criteria won't return any records on 12/31/2005 since the
time_open is after midnight on that date. All that requires is one slight
change to your criteria. Change 12/31/2005 to 1/1/2006. This will get all
records up to 23:59:59 on December 31, 2005 and the time value will then screen
that down to only records within the time period.


SELECT Incident.*
FROM Incident
WHERE (((Incident.time_open)>=#7/1/2005# And
(Incident.time_open)<#1/1/2006#) AND ((TimeValue([time_open]))>=#12/30/1899
8:0:0# And (TimeValue([time_open]))<#12/30/1899 9:0:1#));
Thanks to All for the quick reply!! I was able to use Rick & Marshall's
suggestion and ended up with the following statment:

SELECT Incident.*
FROM Incident
WHERE (((Incident.time_open)>=#7/1/2005# And
(Incident.time_open)<#12/31/2005#) AND ((TimeValue([time_open]))>=#12/30/1899
8:0:0# And (TimeValue([time_open]))<#12/30/1899 9:0:1#));

Works like a charm!!

Thansk again to all!!

Rick Brandt said:
No idexed ay of doing this that I know of. Best I can think of is...

SELECT...
FROM TableName
WHERE DateField >= #2005-07-01#
AND DateField < #2005-12-31#
AND TimeValue(DateField) >= #8:00:00#
AND TimeValue(DateField) < #9:00:01#

The above should be able to use an index for the date criteria, but will have to
scan those rows for the Time criteria.

Since "between 8 and 9 am" is subject to different interpretation I guessed on
what you meant by that. The above would include a record whose time was exactly
9:00:00 am but filter out anything later than that.
Click to expand...
Click to expand...
 
Yes, John, but his original post stated "I need to run a query that finds
all the records between 7/1/05 and 12/30/05", so he should be fine.

BTW, Bill, there's no need to include the 12/30/1899 in the constants:
#8:00:00# or even #8:00# is sufficient.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


John Spencer said:
One nitpick. Your criteria won't return any records on 12/31/2005 since
the
time_open is after midnight on that date. All that requires is one slight
change to your criteria. Change 12/31/2005 to 1/1/2006. This will get
all
records up to 23:59:59 on December 31, 2005 and the time value will then
screen
that down to only records within the time period.


SELECT Incident.*
FROM Incident
WHERE (((Incident.time_open)>=#7/1/2005# And
(Incident.time_open)<#1/1/2006#) AND
((TimeValue([time_open]))>=#12/30/1899
8:0:0# And (TimeValue([time_open]))<#12/30/1899 9:0:1#));
Thanks to All for the quick reply!! I was able to use Rick & Marshall's
suggestion and ended up with the following statment:

SELECT Incident.*
FROM Incident
WHERE (((Incident.time_open)>=#7/1/2005# And
(Incident.time_open)<#12/31/2005#) AND
((TimeValue([time_open]))>=#12/30/1899
8:0:0# And (TimeValue([time_open]))<#12/30/1899 9:0:1#));

Works like a charm!!

Thansk again to all!!

Rick Brandt said:
Bill wrote:
Hi All,

I need to run a query that finds all the records between 7/1/05 and
12/30/05 and only the records between 8:00am and 9:00am. I have the
date range part but I can not figure out the time range part.
Any help would be appreciated Thanks!! Bill

No idexed ay of doing this that I know of. Best I can think of is...

SELECT...
FROM TableName
WHERE DateField >= #2005-07-01#
AND DateField < #2005-12-31#
AND TimeValue(DateField) >= #8:00:00#
AND TimeValue(DateField) < #9:00:01#

The above should be able to use an index for the date criteria, but
will have to
scan those rows for the Time criteria.

Since "between 8 and 9 am" is subject to different interpretation I
guessed on
what you meant by that. The above would include a record whose time
was exactly
9:00:00 am but filter out anything later than that.
Click to expand...
Click to expand...
Click to expand...
 
Yeah, I see that now. I wonder why the OP would not want the last day of the
year when the OP is getting every day of the last half of the year with the
exception of the last day.



Douglas J. Steele said:
Yes, John, but his original post stated "I need to run a query that finds
all the records between 7/1/05 and 12/30/05", so he should be fine.

BTW, Bill, there's no need to include the 12/30/1899 in the constants:
#8:00:00# or even #8:00# is sufficient.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

John Spencer said:
One nitpick. Your criteria won't return any records on 12/31/2005 since
the
time_open is after midnight on that date. All that requires is one slight
change to your criteria. Change 12/31/2005 to 1/1/2006. This will get
all
records up to 23:59:59 on December 31, 2005 and the time value will then
screen
that down to only records within the time period.


SELECT Incident.*
FROM Incident
WHERE (((Incident.time_open)>=#7/1/2005# And
(Incident.time_open)<#1/1/2006#) AND
((TimeValue([time_open]))>=#12/30/1899
8:0:0# And (TimeValue([time_open]))<#12/30/1899 9:0:1#));
Thanks to All for the quick reply!! I was able to use Rick & Marshall's
suggestion and ended up with the following statment:

SELECT Incident.*
FROM Incident
WHERE (((Incident.time_open)>=#7/1/2005# And
(Incident.time_open)<#12/31/2005#) AND
((TimeValue([time_open]))>=#12/30/1899
8:0:0# And (TimeValue([time_open]))<#12/30/1899 9:0:1#));

Works like a charm!!

Thansk again to all!!

:

Bill wrote:
Hi All,

I need to run a query that finds all the records between 7/1/05 and
12/30/05 and only the records between 8:00am and 9:00am. I have the
date range part but I can not figure out the time range part.
Any help would be appreciated Thanks!! Bill

No idexed ay of doing this that I know of. Best I can think of is...

SELECT...
FROM TableName
WHERE DateField >= #2005-07-01#
AND DateField < #2005-12-31#
AND TimeValue(DateField) >= #8:00:00#
AND TimeValue(DateField) < #9:00:01#

The above should be able to use an index for the date criteria, but
will have to
scan those rows for the Time criteria.

Since "between 8 and 9 am" is subject to different interpretation I
guessed on
what you meant by that. The above would include a record whose time
was exactly
9:00:00 am but filter out anything later than that.
Click to expand...
Click to expand...
Click to expand...
 
Bill;

Have you tried ceating a calculated field
=DatePart("h", Now())

You may also want to explore rolling the data over to a Pivot Table in
Excel. This would provide a better method to view the data.
 
Bill;

Have you tried creating a calculated field
=DatePart("h", [Date])

you may also want to explore Pivot Tables in Excel. This may provide a
better method to analyze the data.

HTH
 
Bill;

Have you tried ceating a calculated field
=DatePart("h", Now())

You may also want to explore rolling the data over to a Pivot Table in
Excel. This would provide a better method to view the data.
 
John said:
Yeah, I see that now. I wonder why the OP would not want the last
day of the year when the OP is getting every day of the last half of
the year with the exception of the last day.
Click to expand...

Everyone was out drinking that day?

:-)
 
Bill;

Have you tried ceating a calculated field
=DatePart("h", Now())

You may also want to explore rolling the data over to a Pivot Table in
Excel. This would provide a better method to view the data.
 
Back
Top