Query w/Date & Time in Same Field

[Guest]

Greetings... relative Newbie here.

Working with a database getting external data which has the date & time in
the same field (i.e. 9/20/2007 2:56:00 PM). I am wanting to create a query
to isolate any records between 6:00am and 9:00pm but on any date. Pls Help!!

 

[John W. Vinson]

Greetings... relative Newbie here.

Working with a database getting external data which has the date & time in
the same field (i.e. 9/20/2007 2:56:00 PM). I am wanting to create a query
to isolate any records between 6:00am and 9:00pm but on any date. Pls Help!!

Let's say this field is named TheDateTime. In your Query find a vacant field
cell, and type in it

JustTheTime: TimeValue([TheDateTime])

Put a criterion on this field of

BETWEEN #06:00# AND #09:00#

or for more flexibility,

BETWEEN [Start time:] AND [End time:]

John W. Vinson [MVP]

 

[Guest]

" Put a criterion on this field of BETWEEN #06:00# AND #09:00#" when I use
that criteria I don't get any matching entries when I run the query...
despite the fact that there are matching entries???

Pls advise...
Thx

Greetings... relative Newbie here.

Working with a database getting external data which has the date & time in
the same field (i.e. 9/20/2007 2:56:00 PM). I am wanting to create a query
to isolate any records between 6:00am and 9:00pm but on any date. Pls Help!!

Let's say this field is named TheDateTime. In your Query find a vacant field
cell, and type in it

JustTheTime: TimeValue([TheDateTime])

Put a criterion on this field of

BETWEEN #06:00# AND #09:00#

or for more flexibility,

BETWEEN [Start time:] AND [End time:]

John W. Vinson [MVP]

 

[John Spencer]

John,

Shouldn't the criteria be
Between #06:00:00# and #21:00:00#
(6 AM to 9PM)

Or
Between #06:00:00 AM# and #09:00:00 PM#



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Greetings... relative Newbie here.

Working with a database getting external data which has the date & time in
the same field (i.e. 9/20/2007 2:56:00 PM). I am wanting to create a
query
to isolate any records between 6:00am and 9:00pm but on any date. Pls
Help!!

Let's say this field is named TheDateTime. In your Query find a vacant
field
cell, and type in it

JustTheTime: TimeValue([TheDateTime])

Put a criterion on this field of

BETWEEN #06:00# AND #09:00#

or for more flexibility,

BETWEEN [Start time:] AND [End time:]

John W. Vinson [MVP]

 

[Guest]

Yes, that is actually what I am using. "Between #06:00:00# and #21:00:00#"

Still no results. My table I am using with this query has several entries
every day (throughout typically a one month timeframe) and at various
times... i.e.

"ID Date Time Info (MIN)
27 9/20/2007 7:08:00 PM 0.35 1
28 9/20/2007 7:14:00 PM 0.7 2
29 9/20/2007 9:12:00 PM 2.1 6"

I am trying to pull all entries that occurred between 6am & 9pm regardless
of the date entered.

Alternatively if someone can advise of how to split the date & time into two
separate fields that would also help.

Thanks,

John,

Shouldn't the criteria be
Between #06:00:00# and #21:00:00#
(6 AM to 9PM)

Or
Between #06:00:00 AM# and #09:00:00 PM#



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Greetings... relative Newbie here.

Working with a database getting external data which has the date & time in
the same field (i.e. 9/20/2007 2:56:00 PM). I am wanting to create a
query
to isolate any records between 6:00am and 9:00pm but on any date. Pls
Help!!

Let's say this field is named TheDateTime. In your Query find a vacant
field
cell, and type in it

JustTheTime: TimeValue([TheDateTime])

Put a criterion on this field of

BETWEEN #06:00# AND #09:00#

or for more flexibility,

BETWEEN [Start time:] AND [End time:]

John W. Vinson [MVP]

 

[Guest]

If I include the date info in the search criteria (i.e. "Between #9/21/2007
6:00:00 AM# And #9/21/2007 9:00:00 PM#"), that works... at least for the date
entered there. I suppose I could use "or" and just enter a separate criteria
for every date needed but I am needing to be able to plug in data through-out
the year and would certainly like to avoid having to enter 365 different
query criteria.

Yes, that is actually what I am using. "Between #06:00:00# and #21:00:00#"

Still no results. My table I am using with this query has several entries
every day (throughout typically a one month timeframe) and at various
times... i.e.

"ID Date Time Info (MIN)
27 9/20/2007 7:08:00 PM 0.35 1
28 9/20/2007 7:14:00 PM 0.7 2
29 9/20/2007 9:12:00 PM 2.1 6"

I am trying to pull all entries that occurred between 6am & 9pm regardless
of the date entered.

Alternatively if someone can advise of how to split the date & time into two
separate fields that would also help.

Thanks,

John,

Shouldn't the criteria be
Between #06:00:00# and #21:00:00#
(6 AM to 9PM)

Or
Between #06:00:00 AM# and #09:00:00 PM#



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

On Mon, 22 Oct 2007 19:29:02 -0700, JMcMusicman

Greetings... relative Newbie here.

Working with a database getting external data which has the date & time in
the same field (i.e. 9/20/2007 2:56:00 PM). I am wanting to create a
query
to isolate any records between 6:00am and 9:00pm but on any date. Pls
Help!!

Let's say this field is named TheDateTime. In your Query find a vacant
field
cell, and type in it

JustTheTime: TimeValue([TheDateTime])

Put a criterion on this field of

BETWEEN #06:00# AND #09:00#

or for more flexibility,

BETWEEN [Start time:] AND [End time:]

John W. Vinson [MVP]

 

[John Spencer]

Did you do as John Vinson said and create a new column (field) using the
expression
TimeValue([YourDateTimeField])
?

and then did you apply the criteria against this calculated field?

Field: JustTheTime: TimeValue([YourDateTimeField])
Criteria: Between #06:00:00# and #21:00:00#

By the way that will include any record that has a time of precisely
21:00:00.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

If I include the date info in the search criteria (i.e. "Between
#9/21/2007
6:00:00 AM# And #9/21/2007 9:00:00 PM#"), that works... at least for the
date
entered there. I suppose I could use "or" and just enter a separate
criteria
for every date needed but I am needing to be able to plug in data
through-out
the year and would certainly like to avoid having to enter 365 different
query criteria.

Yes, that is actually what I am using. "Between #06:00:00# and
#21:00:00#"

Still no results. My table I am using with this query has several
entries
every day (throughout typically a one month timeframe) and at various
times... i.e.

"ID Date Time Info (MIN)
27 9/20/2007 7:08:00 PM 0.35 1
28 9/20/2007 7:14:00 PM 0.7 2
29 9/20/2007 9:12:00 PM 2.1 6"

I am trying to pull all entries that occurred between 6am & 9pm
regardless
of the date entered.

Alternatively if someone can advise of how to split the date & time into
two
separate fields that would also help.

Thanks,

John,

Shouldn't the criteria be
Between #06:00:00# and #21:00:00#
(6 AM to 9PM)

Or
Between #06:00:00 AM# and #09:00:00 PM#



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

On Mon, 22 Oct 2007 19:29:02 -0700, JMcMusicman

Greetings... relative Newbie here.

Working with a database getting external data which has the date &
time in
the same field (i.e. 9/20/2007 2:56:00 PM). I am wanting to create a
query
to isolate any records between 6:00am and 9:00pm but on any date.
Pls
Help!!

Let's say this field is named TheDateTime. In your Query find a
vacant
field
cell, and type in it

JustTheTime: TimeValue([TheDateTime])

Put a criterion on this field of

BETWEEN #06:00# AND #09:00#

or for more flexibility,

BETWEEN [Start time:] AND [End time:]

John W. Vinson [MVP]

 

[John Spencer]

Actually, I should have said

Field: JustTheTime:
IIF(IsDate([YourDateTimeField]),TimeValue([YourTimeField]),Null)
Criteria: Between #06:00:00# and #21:00:00#

You use IsDate to prevent errors if the value in the field is Null or cannot
be interpreted as a date.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Did you do as John Vinson said and create a new column (field) using the
expression
TimeValue([YourDateTimeField])
?

and then did you apply the criteria against this calculated field?

Field: JustTheTime: TimeValue([YourDateTimeField])
Criteria: Between #06:00:00# and #21:00:00#

By the way that will include any record that has a time of precisely
21:00:00.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

If I include the date info in the search criteria (i.e. "Between
#9/21/2007
6:00:00 AM# And #9/21/2007 9:00:00 PM#"), that works... at least for the
date
entered there. I suppose I could use "or" and just enter a separate
criteria
for every date needed but I am needing to be able to plug in data
through-out
the year and would certainly like to avoid having to enter 365 different
query criteria.

Yes, that is actually what I am using. "Between #06:00:00# and
#21:00:00#"

Still no results. My table I am using with this query has several
entries
every day (throughout typically a one month timeframe) and at various
times... i.e.

"ID Date Time Info (MIN)
27 9/20/2007 7:08:00 PM 0.35 1
28 9/20/2007 7:14:00 PM 0.7 2
29 9/20/2007 9:12:00 PM 2.1 6"

I am trying to pull all entries that occurred between 6am & 9pm
regardless
of the date entered.

Alternatively if someone can advise of how to split the date & time into
two
separate fields that would also help.

Thanks,


:

John,

Shouldn't the criteria be
Between #06:00:00# and #21:00:00#
(6 AM to 9PM)

Or
Between #06:00:00 AM# and #09:00:00 PM#



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

On Mon, 22 Oct 2007 19:29:02 -0700, JMcMusicman

Greetings... relative Newbie here.

Working with a database getting external data which has the date &
time in
the same field (i.e. 9/20/2007 2:56:00 PM). I am wanting to create
a
query
to isolate any records between 6:00am and 9:00pm but on any date.
Pls
Help!!

Let's say this field is named TheDateTime. In your Query find a
vacant
field
cell, and type in it

JustTheTime: TimeValue([TheDateTime])

Put a criterion on this field of

BETWEEN #06:00# AND #09:00#

or for more flexibility,

BETWEEN [Start time:] AND [End time:]

John W. Vinson [MVP]

 

[Guest]

Excellent!!! Got it!

Thank you both!!

Actually, I should have said

Field: JustTheTime:
IIF(IsDate([YourDateTimeField]),TimeValue([YourTimeField]),Null)
Criteria: Between #06:00:00# and #21:00:00#

You use IsDate to prevent errors if the value in the field is Null or cannot
be interpreted as a date.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Did you do as John Vinson said and create a new column (field) using the
expression
TimeValue([YourDateTimeField])
?

and then did you apply the criteria against this calculated field?

Field: JustTheTime: TimeValue([YourDateTimeField])
Criteria: Between #06:00:00# and #21:00:00#

By the way that will include any record that has a time of precisely
21:00:00.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

If I include the date info in the search criteria (i.e. "Between
#9/21/2007
6:00:00 AM# And #9/21/2007 9:00:00 PM#"), that works... at least for the
date
entered there. I suppose I could use "or" and just enter a separate
criteria
for every date needed but I am needing to be able to plug in data
through-out
the year and would certainly like to avoid having to enter 365 different
query criteria.

:

Yes, that is actually what I am using. "Between #06:00:00# and
#21:00:00#"

Still no results. My table I am using with this query has several
entries
every day (throughout typically a one month timeframe) and at various
times... i.e.

"ID Date Time Info (MIN)
27 9/20/2007 7:08:00 PM 0.35 1
28 9/20/2007 7:14:00 PM 0.7 2
29 9/20/2007 9:12:00 PM 2.1 6"

I am trying to pull all entries that occurred between 6am & 9pm
regardless
of the date entered.

Alternatively if someone can advise of how to split the date & time into
two
separate fields that would also help.

Thanks,


:

John,

Shouldn't the criteria be
Between #06:00:00# and #21:00:00#
(6 AM to 9PM)

Or
Between #06:00:00 AM# and #09:00:00 PM#



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

On Mon, 22 Oct 2007 19:29:02 -0700, JMcMusicman

Greetings... relative Newbie here.

Working with a database getting external data which has the date &
time in
the same field (i.e. 9/20/2007 2:56:00 PM). I am wanting to create
a
query
to isolate any records between 6:00am and 9:00pm but on any date.
Pls
Help!!

Let's say this field is named TheDateTime. In your Query find a
vacant
field
cell, and type in it

JustTheTime: TimeValue([TheDateTime])

Put a criterion on this field of

BETWEEN #06:00# AND #09:00#

or for more flexibility,

BETWEEN [Start time:] AND [End time:]

John W. Vinson [MVP]