Select time only from a date/time field

  • Thread starter Thread starter Kay Starnes
  • Start date Start date
K

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.
 
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
 
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.



John Spencer MVP said:
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 said:
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.
Click to expand...
Click to expand...
 
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#

John Spencer MVP said:
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 said:
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.
Click to expand...
Click to expand...
 
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

Kay said:
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#

John Spencer MVP said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
I used the DateValue expression and returned the date also! Yes, thank you
so much.

John Spencer MVP said:
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 said:
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.
Click to expand...
Click to expand...
 
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!

John Spencer MVP said:
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

Kay said:
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#

John Spencer MVP said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 ?

John Spencer MVP said:
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 said:
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.
Click to expand...
Click to expand...
 
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
'====================================================


Kay said:
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 ?

John Spencer MVP said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top