Date Time Query no pulling correct dates!!

[benwo]

Hi

I have a table with

"starttime", "endtime", "agent", "phonenumb" and a datediff calculation that calculates the diff between "starttime" and "endtime"

the "starttime" and "endtime" columns are in "10/05/2006 12:57:55" format, i.e. the column holds both the date and time

The data is stored in an sql DB and is accessed by an odbc link using Access 2003

I want to be able to run a query within access that lets me chose "agent" and a specific date or a range of dates.

Any help would be greatly appreciated

Thanks

Ben

 

[FoFa]

Since you have a time associated with your date, you need to include time in your queries, or strip the rows of the time. Say you wanted everything for 05/10/2006, you have to say BETWEEN 05/10/2006 00:00:00 AND 05/10/2006 23:59:59 to pickup one full day. If you do not specify a time, it defaults to 00:00:00, and 05/10/2006 07:00:00 is greater than that, and will not be included. Likewise you can strip the date from your rows so they all appear like 05/10/2006 00:00:00, then you only need to sepcify the date portion. Performance wise, specifing the time portion is quicker.