Discussion Locked

Date and Time Field Types

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am working with a database in which the administrator has made a Date and Time field as a "text" type field. Now when I want to define a "between" date parameter, in my query, it won't pull the proper information. The Field is displayed as shown: 12/1/2004 8:00:00 PM
When I use: Between[Start time]And[Finish time] the data pulled is not between the dates I enter. Can I build an expression that recognizes this as a "date and time" and not a "text" type field?

2 replies

by p_attray

Use the function TO_DATE in your query and you can use BETWEEN operator with that. Here's the example...

select * from xxx where to_date('12/1/2004 8:00:00 PM','mm/dd/yyyy hh:mi:ss AM') between x_date and y_date;

Prashant A

by kwis

I should have been more clear. I am not very experienced with Access.
The log_time field is the field I require to do a parameter search but there are multiple fields I require in the query. Will your example still work in converting a text field to a date/time value if I'm running a query on several fields. I guess I'm just not sure on what to enter as the "*" and "xxx".
I hope that makes sense.
Regards,
Kevin

This discussion is closed to new replies.