Querying a Date/Time field for a specific time.

[Dragon]

I have a table that contains a field type Date/Time. This field contains the Date and Time a record was submitted. I want to query this table for all records submitted after 3:00 PM regardless of the date.

When I build a query, I enter >#3:00:00 PM# as the criteria. It returns every record. Even those with times prior to 3:00 PM.

Any suggestions?

Thanks.

 

[Mile-O]

Create a new field like this:


NewField: TimeValue([MyField])

and put the criteria on this field.

 

[Mile-O]

Create a new field like this:


NewField: TimeValue([MyField])

and put the criteria on this field.

 

[Dragon]

That worked, thanks. But there is a different problem now:

When I try a filter the data such as by entering >#3:00:00 PM#, I get the following error:

Data Type Mismatch In Criteria Expression

Any ideas?

 

[Mile-O]

Urgh!

Try this:

NewField: CDate(TimeValue([MyField]))

 

[Dragon]

I figured out what the problem is (was). There were a couple of records in the database that didn’t have any dates. Therefore, when I used the new field you suggested TimeValue([MyField]) there were a couple of records that would contain “Error”. So subsequent filtering would generate a data type mismatch error.

I just entered dates (and then subsequently made the date field mandatory) and the problem solved.

Thanks for your assistance.