Select time only from a date/time field

[Kay Starnes]

I have a table that has date time like dd/mm/yyyy hh:mm:ss AM (not military
time shown with AM or PM beside it) and I need to pull out only the times
from that field that are from the hours of 5pm to 5 am.

Not sure if I need to seperate the date and time fields or if there is a way
to select only a portion of the data...I would like to leave them together
and select what I need if possible.

 

[John Spencer MVP]

TimeValue will return just the time of the datetime field.

WHERE TimeValue(DateTimeField) >= #17:00:00# OR
TimeValue(DateTimeField) <= #05:00:00#

In query design view add a caculated field and place the criteria on two lines
Field: TheTime: TimeValue([DateTimeField])
Criteria(1): >= #17:00:00#
Criteria(2): <= #05:00:00#

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Kay Starnes]

This worked beautifully! However, I now also need to add in the date factor.
I have one table with date/time and need to select the time like below
(works well) but need to add in the date factor. I need to tell it Between
date 1/01/08 and 1/31/08 and the 2 time criteria below. I tried to add the
date in the query field and it seems to ignore the time query then. How do I
acheive both at the same time.

TimeValue will return just the time of the datetime field.

WHERE TimeValue(DateTimeField) >= #17:00:00# OR
TimeValue(DateTimeField) <= #05:00:00#

In query design view add a caculated field and place the criteria on two lines
Field: TheTime: TimeValue([DateTimeField])
Criteria(1): >= #17:00:00#
Criteria(2): <= #05:00:00#

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table that has date time like dd/mm/yyyy hh:mm:ss AM (not military
time shown with AM or PM beside it) and I need to pull out only the times
from that field that are from the hours of 5pm to 5 am.

Not sure if I need to seperate the date and time fields or if there is a way
to select only a portion of the data...I would like to leave them together
and select what I need if possible.

 

[Kay Starnes]

Can you help me add the date selection and the 2 time selections below. I
need to see dates between _ and _ and times >=#17:00:00# or <=#05:00:00#

TimeValue will return just the time of the datetime field.

WHERE TimeValue(DateTimeField) >= #17:00:00# OR
TimeValue(DateTimeField) <= #05:00:00#

In query design view add a caculated field and place the criteria on two lines
Field: TheTime: TimeValue([DateTimeField])
Criteria(1): >= #17:00:00#
Criteria(2): <= #05:00:00#

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table that has date time like dd/mm/yyyy hh:mm:ss AM (not military
time shown with AM or PM beside it) and I need to pull out only the times
from that field that are from the hours of 5pm to 5 am.

Not sure if I need to seperate the date and time fields or if there is a way
to select only a portion of the data...I would like to leave them together
and select what I need if possible.

 

[John Spencer MVP]

Add your DateTimeField to the query a second time

Field: TheDate: DateValue([DateTimeField])
Criteria(1): Between #1/1/08# and #1/31/08#
Criteria(2): Between #1/1/08# and #1/31/08#

Note that the criteria for the date range is exactly the same. The reason you
have to use it twice is so the date range will apply to both time comparisons.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Can you help me add the date selection and the 2 time selections below. I
need to see dates between _ and _ and times >=#17:00:00# or <=#05:00:00#

TimeValue will return just the time of the datetime field.

WHERE TimeValue(DateTimeField) >= #17:00:00# OR
TimeValue(DateTimeField) <= #05:00:00#

In query design view add a caculated field and place the criteria on two lines
Field: TheTime: TimeValue([DateTimeField])
Criteria(1): >= #17:00:00#
Criteria(2): <= #05:00:00#

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table that has date time like dd/mm/yyyy hh:mm:ss AM (not military
time shown with AM or PM beside it) and I need to pull out only the times
from that field that are from the hours of 5pm to 5 am.

Not sure if I need to seperate the date and time fields or if there is a way
to select only a portion of the data...I would like to leave them together
and select what I need if possible.

 

[Kay Starnes]

I used the DateValue expression and returned the date also! Yes, thank you
so much.

TimeValue will return just the time of the datetime field.

WHERE TimeValue(DateTimeField) >= #17:00:00# OR
TimeValue(DateTimeField) <= #05:00:00#

In query design view add a caculated field and place the criteria on two lines
Field: TheTime: TimeValue([DateTimeField])
Criteria(1): >= #17:00:00#
Criteria(2): <= #05:00:00#

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table that has date time like dd/mm/yyyy hh:mm:ss AM (not military
time shown with AM or PM beside it) and I need to pull out only the times
from that field that are from the hours of 5pm to 5 am.

Not sure if I need to seperate the date and time fields or if there is a way
to select only a portion of the data...I would like to leave them together
and select what I need if possible.

 

[Kay Starnes]

Thanks John, Since I used the DateValue 1st it is looking for the date and
then the time so looks like it is working well. I appreciate all your help!

Add your DateTimeField to the query a second time

Field: TheDate: DateValue([DateTimeField])
Criteria(1): Between #1/1/08# and #1/31/08#
Criteria(2): Between #1/1/08# and #1/31/08#

Note that the criteria for the date range is exactly the same. The reason you
have to use it twice is so the date range will apply to both time comparisons.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Can you help me add the date selection and the 2 time selections below. I
need to see dates between _ and _ and times >=#17:00:00# or <=#05:00:00#

TimeValue will return just the time of the datetime field.

WHERE TimeValue(DateTimeField) >= #17:00:00# OR
TimeValue(DateTimeField) <= #05:00:00#

In query design view add a caculated field and place the criteria on two lines
Field: TheTime: TimeValue([DateTimeField])
Criteria(1): >= #17:00:00#
Criteria(2): <= #05:00:00#

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Kay Starnes wrote:
I have a table that has date time like dd/mm/yyyy hh:mm:ss AM (not military
time shown with AM or PM beside it) and I need to pull out only the times
from that field that are from the hours of 5pm to 5 am.

Not sure if I need to seperate the date and time fields or if there is a way
to select only a portion of the data...I would like to leave them together
and select what I need if possible.

 

[Kay Starnes]

John, Crazy thing won't let me change the time, I need to look for peak time
where there is higher volumes and need to segment it out in times like 5pm to
9pm. Access is not recognizing the second part of the criteria correctly.
I tried. 2) <_ #21:00:00# for 9pm. Any chance you know what's wrong with
it, operator ?

TimeValue will return just the time of the datetime field.

WHERE TimeValue(DateTimeField) >= #17:00:00# OR
TimeValue(DateTimeField) <= #05:00:00#

In query design view add a caculated field and place the criteria on two lines
Field: TheTime: TimeValue([DateTimeField])
Criteria(1): >= #17:00:00#
Criteria(2): <= #05:00:00#

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table that has date time like dd/mm/yyyy hh:mm:ss AM (not military
time shown with AM or PM beside it) and I need to pull out only the times
from that field that are from the hours of 5pm to 5 am.

Not sure if I need to seperate the date and time fields or if there is a way
to select only a portion of the data...I would like to leave them together
and select what I need if possible.

 

[John Spencer]

You typed "<_" ?

There is no such comparison operator.
<= or >=
are respectively less than or equal and Greater than or equal. You may
need to use one of those.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John, Crazy thing won't let me change the time, I need to look for peak time
where there is higher volumes and need to segment it out in times like 5pm to
9pm. Access is not recognizing the second part of the criteria correctly.
I tried. 2) <_ #21:00:00# for 9pm. Any chance you know what's wrong with
it, operator ?

TimeValue will return just the time of the datetime field.

WHERE TimeValue(DateTimeField) >= #17:00:00# OR
TimeValue(DateTimeField) <= #05:00:00#

In query design view add a caculated field and place the criteria on two lines
Field: TheTime: TimeValue([DateTimeField])
Criteria(1): >= #17:00:00#
Criteria(2): <= #05:00:00#

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a table that has date time like dd/mm/yyyy hh:mm:ss AM (not military
time shown with AM or PM beside it) and I need to pull out only the times
from that field that are from the hours of 5pm to 5 am.

Not sure if I need to seperate the date and time fields or if there is a way
to select only a portion of the data...I would like to leave them together
and select what I need if possible.