Date Range Query Problem

[Peter Hibbs]

Dale,

It isn't. I changed it to Indexed (Duplicates OK) but it only made a
small difference, about 1 second quicker.

Peter Hibbs.

Peter,

Is the StartDate field indexed? If not, create an index on that field, and
try the queries again.

Allen, Dale & Ken,

FYI, Update on my query problem.

All the proposed solutions worked OK (after I fixed the Null StartDate
fields) but the problem then is that they all were very slow, approx
10 seconds to execute the query on my 3GHz Pentium PC. I didn't
mention it originally but I need to do the same for two other tables
(General policies and Life policies). My client has even slower PCs on
a Network so I suspect that this delay would be annoying and probably
unacceptable for him.

I did some more testing and I found that by checking the table data in
a recordset the execution time was almost instantaneous. Remember I am
only looking for a count of the records that are relevant and am not
interested in the data in the table (apart from the Start Date field).
The code I used is :-

Dim vCount As Long
Dim rst As Recordset
Dim vDate As Date, vStart As Date, vEnd As Date

vCount = 0
vStart = Forms!frmReports!txtStartDate
vEnd = Forms!frmReports!txtEndDate
Set rst = CurrentDb.OpenRecordset("SELECT StartDate FROM tblMotor
WHERE StartDate IS NOT NULL", dbOpenSnapshot)
Do Until rst.EOF
vDate = DateSerial(Year(vStart), Month(rst!STARTDATE),
Day(rst!STARTDATE))
If vDate < vStart Then vDate = DateAdd("yyyy", 1, vDate)
If vDate >= vStart And vDate <= vEnd Then vCount = vCount + 1
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
txtMotor = vCount

I think it shows that a query is not always the best solution even
though it may be the simplest. Anyway, thanks again for the
assistance, I have learnt a bit more about writing queries as a result
of all your replies.

Peter Hibbs.

Allen,

Sorry, but that does not seem to work (unless I have done something
wrong). I created a new table called tblCount and added 9 records with
the CountID set to 0, 1, 2, 3, etc

The query looks like this :-

SELECT tblMotor.ID,
DateAdd("yyyy",[tblCount].[CountID],[tblMotor].[StartDate]) AS DueDate
FROM tblMotor, tblCount
WHERE (((DateAdd("yyyy",[tblCount].[CountID],[tblMotor].[StartDate]))
Between [Forms]![frmReports]![txtStartDate] And
[Forms]![frmReports]![txtEndDate]));

It is returning too many records. If I set the date range to 1 Sep
2007 to 30 Sep 2007 it should ONLY return records where the StartDate
is in September, i.e. 1/9/1989, 11/9/2002, 20/9/2005 and so on. It
is returning all records from 1/9/2007 to 30/9/2008 with all months in
between. I suspect the query is doing what you intended but that is
not what I need.

Any other ideas?

Peter.

On Wed, 10 Sep 2008 22:13:02 +0800, "Allen Browne"

One way to do this would be with a Cartesian product query.

You will need a table with one Number field (primary key), populated with
the numbers from 0 to the maximum number of years you could need to
consider
(perhaps 100.) Let's call it tblCount, with the field named CountID.

Create a query using both your policy table and tblCount. In the upper
pane
of table design, you will have both tables, but no line joining them.
This
gives you every possible combination. Now type this expression into the
Field row:
DueDate: DateAdd("yyyy", tblCount.CountID, tblMotor.StartDate)
This will give you a record for every time the policy is due, from the
original start date for the next 100 years.

Now add the criteria under this field (one one line):
Between [Forms]![frmReports]![txtStartDate]
And [Forms]![frmReports]![txtEndDate]));

There are other solutions (such as DateSerial()), but a major advantage
of
this approach is that you can examine any period - even periods longer
than
12 months.