Full Version: times for query criteria
UtterAccess Forums > Microsoft® Access > Access Queries
I cannot figure out how to write criteria to get a query to return all records with elapsed time between 1 second and 20 seconds.
I have a [start time] field and an [end time] field that I use to calculate elapsed time. What should I put in the query criteria to get all records with elapsed time between 00:00:01 and 00:20:00?
If your field [elapsed time] is recorded in seconds, then something like this should work
Select * from MyTable
[elapsed time] between 1 and 20
What is your table structure?
Can you provide some sample data/results?
Hi LizH,
Welcome to Utter Access!
Looking at your sample data makes me wonder if you are storing your elapsed time as a date/time field. If so, you should change it to a Long data type and the suggestion already given should work.
Just my 2 cents...
The start time and end time fields in my table are date/time. When I changed the date/time fields to long integer each time is now either a 1 or 0. I'm attaching a small sample table. Whatever help anyone can give I appreciate very much.
heck out the attached modified version and open up the query. Let us know if it's not what you're looking for.
Hope that helps...
Try this one that I use.
>>...with elapsed time between 00:00:01 and 00:20:00?

The higher limit looks like 20 minutes to me???

If [StartTime] and [EndTime] are of DateTime data type, try:
FROM [YourTable] As T1
WHERE DateDiff("s", [StartTime], [EndTime]) Between 1 And 20
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.