Date Range Query Issue

[Noah114]

I have a query in which I need to specify a date range. At this time, I manually place in the range with a between statement. What I would like to do is store the date range in a table and have the query run off of that range but when I do, the query takes hours to run. When I manually put it in, about 5 minutes.

I have one table, that's an ODBC connection and another table 'Date Range Input' with the date info with a start and end date field. The ODBC table has one date field, "FILL DATE". In the query under the FILL DATE criteria I have:

Between [Date Range Input.StartDate] And DateAdd("s",-1,CDate(Int(CDbl(DateAdd('d',1,[Date Range Input.EndDate])))))

When I use this, it takes forever!!! Why the big difference in timing from manual to lookup?

Is there an easier/faster way. Any assistance would be great!

 

[PHV]

Have you tried this ?
Between [Date Range Input].StartDate And 1 + [Date Range Input].StartDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Noah114]

Did you mean;

Between [Date Range Input].StartDate And 1 + [Date Range Input].EndDate (Instead of Start Date twice)

 

[OhioSteve]

Here is my understanding of the problem:

You have a table with data in it. It is a linked table, and the connection type is ODBC. It has one field, a data time field called "FILL DATE". Let's call it main.

You have a table called "date range input". It has two fields, startDate and Enddate. It just has one record, and you use it to provide criteria for a query on main.

The query takes hours to run.

Here are my questions:

Do I understand your last post correctly?

When you open main manually, does it open immediately? If not, how long is the lag?

How many records are in main?

Here is an immediate suggestion for you:

The second part of the between statement is really convuluted. I think you are adding a day, then subtracting a second. Perhaps you could stop using between. Instead use two independent criteria > and <. Each of them can just be dates. So if you want to get Mar 10-15, just say "greater than March 9 and less than march 16".