query on time

[Warren Van Duesbury]

I am trying to perform a query based on time values.
The field in a table (MyTable) is called 'MyTime' and is set with the Date datatype and formatted to show "hh:nn". Each record is entered/(or appears)in half hour increments like 09:00, 09:30, 10:00, 10:30 etc.
I have a form that Users enter a start time ie: 10:02 and a Finish time, ie: 10:37. The results I want in my query would be 10:00 and 10:30. In English I'm trying to say "Show me the records im MyTable where the times on my form fall into the half hour records in MyTable".
I have tried all sorts of things such as - Between Format([Forms]![frmIncident]![txtStart],"hh:nn") And Format([Forms]![frmIncident]![txtFinish],"hh:nn") and all other sorts of variations on a theme including functions as query criteria, parsing the time but I still can't seem to find an effective solution. If anyone has the time or interest to look at this I'd be extemely grateful.


[This message has been edited by Warren Van Duesbury (edited 11-17-2000).]

 

[chrismcbride]

Warren
I can't say that I completely understand the problem, but I do have a couple of general things to mention.
1. The Format function takes the supplied date or number value and converts it to a string using the format parameters passed to it. This would render the BETWEEN function inoperable, since it can not evaluate strings, I believe.
2. I am not sure what you want to do with the return of you query, but if it is for display you could simply format the returned date. Use the BETWEEN function to return the dates you need, then use a IIF function to display a calculated value.
Hope this helps
Chris