Date/time query issues

[bimthecon]

Hi all,

Im trying to run a query based on a field which contains both the date
and time value and I want to specify criteria that will pull all
records for a given time frame regardless of date. I have tried using
the format() function which I know brings back a string value that is
diffucult to evaluate correctly. I have also tried using the
timevalue() function which works but will not let me specify any
criteria on that field. It gives me a data type mismatch error. The
criteria I have tried using:

#12:00:00 AM#

between #12:00:00 AM# and #5:00:00 AM#

I have also tried submitting these with quotes instead of the # sign
but I still get the mismatch error. Thanks for taking the time to
read this post and for any help that can be provided.

 

[John Spencer]

This should work

Field: TimeValue([YourFieldName])
Criteria: Between #00:00:00# and #05:00:00#

If not, try

Field: IIF(IsDate([YourFieldName]), TimeValue([YourFieldName]),Null)
Criteria: Between #00:00:00# and #05:00:00#


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

 

[bimthecon]

This should work

Field: TimeValue([YourFieldName])
Criteria: Between #00:00:00# and #05:00:00#

If not, try

Field: IIF(IsDate([YourFieldName]), TimeValue([YourFieldName]),Null)
Criteria: Between #00:00:00# and #05:00:00#

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

Hi all,

Im trying to run a query based on a field which contains both the date
and time value and I want to specify criteria that will pull all
records for a given time frame regardless of date. I have tried using
the format() function which I know brings back a string value that is
diffucult to evaluate correctly. I have also tried using the
timevalue() function which works but will not let me specify any
criteria on that field. It gives me a data type mismatch error. The
criteria I have tried using:

between #12:00:00 AM# and #5:00:00 AM#

I have also tried submitting these with quotes instead of the # sign
but I still get the mismatch error. Thanks for taking the time to
read this post and for any help that can be provided.- Hide quoted text -

- Show quoted text -

Thanks so much, the if statement did the trick. I take it that this
means that one or more of the records had an invalid date?

 

[John Spencer]

Probably one or more of the fields is null. TimeValue will error if the
field value is null.

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

This should work

Field: TimeValue([YourFieldName])
Criteria: Between #00:00:00# and #05:00:00#

If not, try

Field: IIF(IsDate([YourFieldName]), TimeValue([YourFieldName]),Null)
Criteria: Between #00:00:00# and #05:00:00#

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

Hi all,

Im trying to run a query based on a field which contains both the date
and time value and I want to specify criteria that will pull all
records for a given time frame regardless of date. I have tried using
the format() function which I know brings back a string value that is
diffucult to evaluate correctly. I have also tried using the
timevalue() function which works but will not let me specify any
criteria on that field. It gives me a data type mismatch error. The
criteria I have tried using:

#12:00:00 AM#

between #12:00:00 AM# and #5:00:00 AM#

I have also tried submitting these with quotes instead of the # sign
but I still get the mismatch error. Thanks for taking the time to
read this post and for any help that can be provided.- Hide quoted
text -

- Show quoted text -

Thanks so much, the if statement did the trick. I take it that this
means that one or more of the records had an invalid date?