Can't get same results on query using dynamic time frame versus static time frame

[kozpirnie]

I am trying to run a query on a Lookout database using MS Access. If I use static dates as criteria in the "LocalTime" field I get results that are marked on the hour, eg. 1:00 pm. If I try to use dynamic dates as criteria I get results that are marked on the half hour, eg. 1:30 pm. The results are for each hour but the difference in the queries causes the dynamic query to come up with different results than a spreadsheet generated by Lookout that the client relies on for backup. Does anybody know a way I can get the dynamic date query to act like the static query? I can't use static queries because this is for a daily report and the client will not pay for me to make 365 new queries each y
ear. I tried NI's tech support but they pointed me to the same SQL examples that are in the Developer's Manual which are all static queries. Tech Support also told me they can't help me figure out dynamic queries.

[Rkdomer]

Are you using the "Intraval" clause in the SQL statement? That would be something like "select traces.something from traces.traces where localtime > date and localtime < date and interval = 1:00". Also, the localtime clause can be date and time as in "12/01/03 00:00". That will insure the result table is started on the hour.

[kozpirnie]

Yes I am using an interval clause that says "interval=1:00". I create the localtime clause dynamically and this seems to be where my problem lies. No matter what I have the dynamic function create the date/time as, i.e., 07/01/2002 or 07/01/2002 12:00 AM or 07/01/2002 00:00, the time the query returns for each record is 32 minutes after the hour. From this I assume it is averaging each hour interval from one half hour mark to the next, eg. from 12:30 am to 1:30 am. This is born out because the hourly averages do not match the averages for the same time frame that Lookout exports to an Excel spreadsheet. Thanks for your suggestion though.