Date Range Query Problem

[Peter Hibbs]

I have a table (tblMotor) which holds the Motor Insurance Policies for
my client's company. The table has (amongst others) two fields, ID
(AutoNumber) and StartDate (Date/Time). The StartDate field holds the
date the policy was started which means that the renewal date for each
policy will be the same date in each subsequent year.

I also have a form (frmReports) which has two unbound fields -
txtStartDate and txtEndDate. My client wants to show on a report the
total number of policies that were started in a given period (usually
a 3 month period but could vary) where the start and end dates are
specified in the two Text fields.

This I have done with this query :-

SELECT Count(tblMotor.ID) AS CountOfID
FROM tblMotor
WHERE (((tblMotor.StartDate) Between
[Forms]![frmReports]![txtStartDate] And
[Forms]![frmReports]![txtEndDate]));

He also wants to show the total number of policies that are due for
renewal in the same period, that is where the day/month of the
policies fall within the specified period. For example, if the period
is set to 1 Nov 2008 to 16 Jan 2009 he wants a total count of the
number of records where the StartDate falls between those dates
regardless of the actual year. I can't see any easy way to do that,
anyone got any ideas.

Peter Hibbs.

 

[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.

 

[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.

 

[Allen Browne]

Sounds like Access is not interpreting your dates correctly.

1. In form design view, set the Format property of both text boxes to Short
Date so Access knows they are dates.

2. In query design view, choose Parameters on the Query menu.
Enter 2 rows in the dialog:
[Forms]![frmReports]![txtStartDate] Date/Time
[Forms]![frmReports]![txtEndDate] Date/Time

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

 

[Peter Hibbs]

Allen,

OK, that made a difference but does not do what I need. It is only
returning dates within the specified year, i.e. if I set the dates to
1/9/2007 to 30/9/2007 it returns dates within those two dates. What I
am trying to do is get it to return EVERY policy that has a StartDate
between 1/9/???? and 30/9/???? The actual year is irrelevant, if a
policy was started on 20/9/1994 it will next be due for renewal on
20/9/2008. So if I set the date range on the form to be 1/9/2008 to
30/9/2008 I want that policy to be included in the count along with
one that was started on 20/9/1999 and one started on 27/9/2007 and so
on. Does that clarify the problem more?

Peter Hibbs.

 

[Dale Fye]

How about something like:

SELECT * FROM tblMotor
WHERE Format(tblMotor.StartDate, "mm dd")
BETWEEN Format(Forms!frmReports.txtStartDate, "mm dd")
AND Format(Forms!frmReports.txtEndDate, "mm dd")

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Allen,

OK, that made a difference but does not do what I need. It is only
returning dates within the specified year, i.e. if I set the dates to
1/9/2007 to 30/9/2007 it returns dates within those two dates. What I
am trying to do is get it to return EVERY policy that has a StartDate
between 1/9/???? and 30/9/???? The actual year is irrelevant, if a
policy was started on 20/9/1994 it will next be due for renewal on
20/9/2008. So if I set the date range on the form to be 1/9/2008 to
30/9/2008 I want that policy to be included in the count along with
one that was started on 20/9/1999 and one started on 27/9/2007 and so
on. Does that clarify the problem more?

Peter Hibbs.

Sounds like Access is not interpreting your dates correctly.

1. In form design view, set the Format property of both text boxes to Short
Date so Access knows they are dates.

2. In query design view, choose Parameters on the Query menu.
Enter 2 rows in the dialog:
[Forms]![frmReports]![txtStartDate] Date/Time
[Forms]![frmReports]![txtEndDate] Date/Time

 

[Peter Hibbs]

Dale,

Well, it looked good - at first - but it doesn't quite work. What
happens is this - if the date range is within the same year it
(correctly) returns just the dates within that date range for all
years. However, if the date range crosses a year boundary it falls
over spectacularly, that is it returns dates for every month.

The idea is that the date range will normally be set (by default) to
cover the next three months starting from the current date so that the
user can estimate work load for that period. I have also given them
the option to enter any date range they want to give them a bit more
flexibility. This means that the range could cover November to
January, December to February and so on.

Thanks for your interest, is it possible to tweak you code to fix that
problem. Perhaps I am going to have to resort to some VBA code!

Peter Hibbs.

 

[Ken Sheridan]

The problem is that you need to cater for ranges which span the end of the
year. I'd suggest adding a function along the following lines to a standard
module , which computes dummy dates for the purpose of testing if a date is
within the range whatever the year:

Public Function Renewal(dtmPolicyStart As Date, _
dtmRangeStart As Date, _
dtmRangeEnd As Date) As Boolean

Dim dtmDummyPolicyStart As Date
Dim dtmDummyRangeStart As Date
Dim dtmDummyRangeEnd As Date


' compute dummy dates
dtmDummyPolicyStart = _
DateSerial(2000, Month(dtmPolicyStart), Day(dtmPolicyStart))

dtmDummyRangeStart = _
DateSerial(2000, Month(dtmRangeStart), Day(dtmRangeStart))

dtmDummyRangeEnd = _
DateSerial(2000 + Year(dtmRangeEnd) - _
Year(dtmRangeStart), Month(dtmRangeEnd), _
Day(dtmRangeEnd))

' if policy started within range return True
If dtmDummyPolicyStart >= dtmDummyRangeStart _
And dtmDummyPolicyStart < _
DateAdd("d", 1, dtmDummyRangeEnd) Then

Renewal = True

End If

End Function

You can then call it in a query like so:

PARAMETERS
[Forms]![frmReports]![txtStartDate] DATETIME,
[Forms]![frmReports]![txtEndDate] DATETIME;
SELECT COUNT(*) AS CountOfPolicies
FROM tblMotor
WHERE Renewal(StartDate,
[Forms]![frmReports]![txtStartDate],
[Forms]![frmReports]![txtEndDate]);

Ken Sheridan
Stafford, England

Allen,

OK, that made a difference but does not do what I need. It is only
returning dates within the specified year, i.e. if I set the dates to
1/9/2007 to 30/9/2007 it returns dates within those two dates. What I
am trying to do is get it to return EVERY policy that has a StartDate
between 1/9/???? and 30/9/???? The actual year is irrelevant, if a
policy was started on 20/9/1994 it will next be due for renewal on
20/9/2008. So if I set the date range on the form to be 1/9/2008 to
30/9/2008 I want that policy to be included in the count along with
one that was started on 20/9/1999 and one started on 27/9/2007 and so
on. Does that clarify the problem more?

Peter Hibbs.

Sounds like Access is not interpreting your dates correctly.

1. In form design view, set the Format property of both text boxes to Short
Date so Access knows they are dates.

2. In query design view, choose Parameters on the Query menu.
Enter 2 rows in the dialog:
[Forms]![frmReports]![txtStartDate] Date/Time
[Forms]![frmReports]![txtEndDate] Date/Time

 

[Peter Hibbs]

Ken,

Tried it but get error message when I run the query.

"The expression is typed incorrectly or is too complex to evaluate.
For example, a numeric expression may contain too many complicated
elements. Try simplifying the expresson by assigning parts of the
expression to variables."

I will have another look later (just going to have some dinner) but I
think you are right, it is going to need a code function to process
the data.

If you have any thoughts please let me know.

Peter Hibbs.

The problem is that you need to cater for ranges which span the end of the
year. I'd suggest adding a function along the following lines to a standard
module , which computes dummy dates for the purpose of testing if a date is
within the range whatever the year:

Public Function Renewal(dtmPolicyStart As Date, _
dtmRangeStart As Date, _
dtmRangeEnd As Date) As Boolean

Dim dtmDummyPolicyStart As Date
Dim dtmDummyRangeStart As Date
Dim dtmDummyRangeEnd As Date


' compute dummy dates
dtmDummyPolicyStart = _
DateSerial(2000, Month(dtmPolicyStart), Day(dtmPolicyStart))

dtmDummyRangeStart = _
DateSerial(2000, Month(dtmRangeStart), Day(dtmRangeStart))

dtmDummyRangeEnd = _
DateSerial(2000 + Year(dtmRangeEnd) - _
Year(dtmRangeStart), Month(dtmRangeEnd), _
Day(dtmRangeEnd))

' if policy started within range return True
If dtmDummyPolicyStart >= dtmDummyRangeStart _
And dtmDummyPolicyStart < _
DateAdd("d", 1, dtmDummyRangeEnd) Then

Renewal = True

End If

End Function

You can then call it in a query like so:

PARAMETERS
[Forms]![frmReports]![txtStartDate] DATETIME,
[Forms]![frmReports]![txtEndDate] DATETIME;
SELECT COUNT(*) AS CountOfPolicies
FROM tblMotor
WHERE Renewal(StartDate,
[Forms]![frmReports]![txtStartDate],
[Forms]![frmReports]![txtEndDate]);

Ken Sheridan
Stafford, England

Allen,

OK, that made a difference but does not do what I need. It is only
returning dates within the specified year, i.e. if I set the dates to
1/9/2007 to 30/9/2007 it returns dates within those two dates. What I
am trying to do is get it to return EVERY policy that has a StartDate
between 1/9/???? and 30/9/???? The actual year is irrelevant, if a
policy was started on 20/9/1994 it will next be due for renewal on
20/9/2008. So if I set the date range on the form to be 1/9/2008 to
30/9/2008 I want that policy to be included in the count along with
one that was started on 20/9/1999 and one started on 27/9/2007 and so
on. Does that clarify the problem more?

Peter Hibbs.

Sounds like Access is not interpreting your dates correctly.

1. In form design view, set the Format property of both text boxes to Short
Date so Access knows they are dates.

2. In query design view, choose Parameters on the Query menu.
Enter 2 rows in the dialog:
[Forms]![frmReports]![txtStartDate] Date/Time
[Forms]![frmReports]![txtEndDate] Date/Time

 

[Dale Fye]

OK, lets try this again.

You need to look at this from the perspective that you want to identify
those records where the anniversary of that date falls between two dates.

To do this type of thing, I have a table (tbl_Numbers) which has a single
field (lngValue), and which contains the numbers 0 through 9.

I then create a query (qry_Numbers) from this table that looks like:

SELECT Hundreds.lngValue * 100 + Tens.lngValue * 10 + Ones.lngValue as
lngValue
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones

With this new table and query, you should be able to do:

SELECT tblMotor.*, qry_Numbers.lngValue as Anniversary
FROM tblMotor, qry_Numbers
WHERE DateSerial(Year(tblMotor.StartDate) + qry_Numbers.lngValue,
Month(tblMotor.StartDate),
Day(tblMotor.StartDate)
BETWEEN Forms!frmReports.txtStartDate
AND Forms!frmReports.txtEndDate

Additionally, this will also identify which anniversary it is for the policy.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Actually,

I think this was sort of what Allen was addressing in his original post.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Peter Hibbs]

Dale,

I can't get it to work. Did the table and the two queries. I think
there is an error in the second query as there is an odd number of
parentheses. I added a ) to the end of the DateSerial command to fix
that (not sure if that is what you meant). The problem now is that
when I run the query I get this error twice :-

Data type mismatch in criteria expression.

The query looks like this now :-

PARAMETERS [Forms]![frmReports].[txtStartDate] DateTime,
[Forms]![frmReports].[txtEndDate] DateTime;
SELECT tblMotor.*, qry_Numbers.lngValue AS Anniversary
FROM tblMotor, qry_Numbers
WHERE
(((DateSerial(Year([tblMotor].[StartDate])+[qry_Numbers].[lngValue],Month([tblMotor].[StartDate]),Day([tblMotor].[StartDate])))
Between [Forms]![frmReports].[txtStartDate] And
[Forms]![frmReports].[txtEndDate]));

The lngValue is a Long Integer so I'm a bit puzzled as to what
mismatch is in there.As you can see I also defined the two Text fields
as Date/Time types in the Parameters property of the query (as Allen
suggested) but that makes no difference.

Any ideas?

Peter Hibbs.

 

[Ken Sheridan]

Peter:

I've tested it and the query runs OK, but I did find that I'd not taken
account of where the date range spans the end of the year and the policy date
comes before the start of the range. After debugging it I think this
amendment should cater for that scenario:

Public Function Renewal(dtmPolicyStart As Date, _
dtmRangeStart As Date, _
dtmRangeEnd As Date) As Boolean

Dim dtmDummyPolicyStart As Date
Dim dtmDummyRangeStart As Date
Dim dtmDummyRangeEnd As Date


' compute dummy dates
dtmDummyPolicyStart = _
DateSerial(2000, Month(dtmPolicyStart), Day(dtmPolicyStart))

dtmDummyRangeStart = _
DateSerial(2000, Month(dtmRangeStart), Day(dtmRangeStart))

dtmDummyRangeEnd = _
DateSerial(2000 + Year(dtmRangeEnd) - _
Year(dtmRangeStart), Month(dtmRangeEnd), _
Day(dtmRangeEnd))

' adjust for dates before start of range where range
' spans end of year
If Year(dtmDummyRangeEnd) > Year(dtmDummyRangeStart) Then
If dtmDummyPolicyStart < dtmDummyRangeStart Then
dtmDummyPolicyStart = DateAdd("yyyy", 1, dtmDummyPolicyStart)
End If
End If

' if policy started within range return True
If dtmDummyPolicyStart >= dtmDummyRangeStart _
And dtmDummyPolicyStart < _
DateAdd("d", 1, dtmDummyRangeEnd) Then

Renewal = True

End If

End Function

The only reason that springs to mind which might possibly account for the
error is if you've given the module the same name as the function. If so
then change the module name, using a suitable naming convention, to
mdlRenewal say. I'm doubtful that this is the cause, however, as I'd expect
an 'undefined function' error in that case, but I can't see any other
potential cause at first sight. Check the SQL of the query carefully and
make sure it exactly matches that which I posted.

Ken Sheridan
Stafford, England

Ken,

Tried it but get error message when I run the query.

"The expression is typed incorrectly or is too complex to evaluate.
For example, a numeric expression may contain too many complicated
elements. Try simplifying the expresson by assigning parts of the
expression to variables."

I will have another look later (just going to have some dinner) but I
think you are right, it is going to need a code function to process
the data.

If you have any thoughts please let me know.

Peter Hibbs.

The problem is that you need to cater for ranges which span the end of the
year. I'd suggest adding a function along the following lines to a standard
module , which computes dummy dates for the purpose of testing if a date is
within the range whatever the year:

Public Function Renewal(dtmPolicyStart As Date, _
dtmRangeStart As Date, _
dtmRangeEnd As Date) As Boolean

Dim dtmDummyPolicyStart As Date
Dim dtmDummyRangeStart As Date
Dim dtmDummyRangeEnd As Date


' compute dummy dates
dtmDummyPolicyStart = _
DateSerial(2000, Month(dtmPolicyStart), Day(dtmPolicyStart))

dtmDummyRangeStart = _
DateSerial(2000, Month(dtmRangeStart), Day(dtmRangeStart))

dtmDummyRangeEnd = _
DateSerial(2000 + Year(dtmRangeEnd) - _
Year(dtmRangeStart), Month(dtmRangeEnd), _
Day(dtmRangeEnd))

' if policy started within range return True
If dtmDummyPolicyStart >= dtmDummyRangeStart _
And dtmDummyPolicyStart < _
DateAdd("d", 1, dtmDummyRangeEnd) Then

Renewal = True

End If

End Function

You can then call it in a query like so:

PARAMETERS
[Forms]![frmReports]![txtStartDate] DATETIME,
[Forms]![frmReports]![txtEndDate] DATETIME;
SELECT COUNT(*) AS CountOfPolicies
FROM tblMotor
WHERE Renewal(StartDate,
[Forms]![frmReports]![txtStartDate],
[Forms]![frmReports]![txtEndDate]);

Ken Sheridan
Stafford, England

Allen,

OK, that made a difference but does not do what I need. It is only
returning dates within the specified year, i.e. if I set the dates to
1/9/2007 to 30/9/2007 it returns dates within those two dates. What I
am trying to do is get it to return EVERY policy that has a StartDate
between 1/9/???? and 30/9/???? The actual year is irrelevant, if a
policy was started on 20/9/1994 it will next be due for renewal on
20/9/2008. So if I set the date range on the form to be 1/9/2008 to
30/9/2008 I want that policy to be included in the count along with
one that was started on 20/9/1999 and one started on 27/9/2007 and so
on. Does that clarify the problem more?

Peter Hibbs.

On Wed, 10 Sep 2008 23:27:36 +0800, "Allen Browne"

Sounds like Access is not interpreting your dates correctly.

1. In form design view, set the Format property of both text boxes to Short
Date so Access knows they are dates.

2. In query design view, choose Parameters on the Query menu.
Enter 2 rows in the dialog:
[Forms]![frmReports]![txtStartDate] Date/Time
[Forms]![frmReports]![txtEndDate] Date/Time

 

[Dale Fye]

It looks right, and I tested it with some test data I created. Worked as
expected.

My first guess is you have some NULL values in your StartDate field.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale,

I can't get it to work. Did the table and the two queries. I think
there is an error in the second query as there is an odd number of
parentheses. I added a ) to the end of the DateSerial command to fix
that (not sure if that is what you meant). The problem now is that
when I run the query I get this error twice :-

Data type mismatch in criteria expression.

The query looks like this now :-

PARAMETERS [Forms]![frmReports].[txtStartDate] DateTime,
[Forms]![frmReports].[txtEndDate] DateTime;
SELECT tblMotor.*, qry_Numbers.lngValue AS Anniversary
FROM tblMotor, qry_Numbers
WHERE
(((DateSerial(Year([tblMotor].[StartDate])+[qry_Numbers].[lngValue],Month([tblMotor].[StartDate]),Day([tblMotor].[StartDate])))
Between [Forms]![frmReports].[txtStartDate] And
[Forms]![frmReports].[txtEndDate]));

The lngValue is a Long Integer so I'm a bit puzzled as to what
mismatch is in there.As you can see I also defined the two Text fields
as Date/Time types in the Parameters property of the query (as Allen
suggested) but that makes no difference.

Any ideas?

Peter Hibbs.

Actually,

I think this was sort of what Allen was addressing in his original post.

 

[Ken Sheridan]

Peter:

I think Dale's probably hit the nail on the head and its Null start dates
that are the problem. That's consistent with the error. Change the function
to:

Public Function Renewal(varPolicyStart As Variant, _
dtmRangeStart As Date, _
dtmRangeEnd As Date) As Boolean

Dim dtmDummyPolicyStart As Date
Dim dtmDummyRangeStart As Date
Dim dtmDummyRangeEnd As Date

If Not IsNull(varPolicyStart) Then
' compute dummy dates
dtmDummyPolicyStart = _
DateSerial(2000, Month(varPolicyStart), Day(varPolicyStart))

dtmDummyRangeStart = _
DateSerial(2000, Month(dtmRangeStart), Day(dtmRangeStart))

dtmDummyRangeEnd = _
DateSerial(2000 + Year(dtmRangeEnd) - _
Year(dtmRangeStart), Month(dtmRangeEnd), _
Day(dtmRangeEnd))

' adjust for dates before start of range where range
' spans end of year
If Year(dtmDummyRangeEnd) > Year(dtmDummyRangeStart) Then
If dtmDummyPolicyStart < dtmDummyRangeStart Then
dtmDummyPolicyStart = DateAdd("yyyy", 1, dtmDummyPolicyStart)
End If
End If

' if policy started within range return True
If dtmDummyPolicyStart >= dtmDummyRangeStart _
And dtmDummyPolicyStart < _
DateAdd("d", 1, dtmDummyRangeEnd) Then

Renewal = True

End If
End If

End Function


Ken Sheridan
Stafford, England

Ken,

Tried it but get error message when I run the query.

"The expression is typed incorrectly or is too complex to evaluate.
For example, a numeric expression may contain too many complicated
elements. Try simplifying the expresson by assigning parts of the
expression to variables."

I will have another look later (just going to have some dinner) but I
think you are right, it is going to need a code function to process
the data.

If you have any thoughts please let me know.

Peter Hibbs.

The problem is that you need to cater for ranges which span the end of the
year. I'd suggest adding a function along the following lines to a standard
module , which computes dummy dates for the purpose of testing if a date is
within the range whatever the year:

Public Function Renewal(dtmPolicyStart As Date, _
dtmRangeStart As Date, _
dtmRangeEnd As Date) As Boolean

Dim dtmDummyPolicyStart As Date
Dim dtmDummyRangeStart As Date
Dim dtmDummyRangeEnd As Date


' compute dummy dates
dtmDummyPolicyStart = _
DateSerial(2000, Month(dtmPolicyStart), Day(dtmPolicyStart))

dtmDummyRangeStart = _
DateSerial(2000, Month(dtmRangeStart), Day(dtmRangeStart))

dtmDummyRangeEnd = _
DateSerial(2000 + Year(dtmRangeEnd) - _
Year(dtmRangeStart), Month(dtmRangeEnd), _
Day(dtmRangeEnd))

' if policy started within range return True
If dtmDummyPolicyStart >= dtmDummyRangeStart _
And dtmDummyPolicyStart < _
DateAdd("d", 1, dtmDummyRangeEnd) Then

Renewal = True

End If

End Function

You can then call it in a query like so:

PARAMETERS
[Forms]![frmReports]![txtStartDate] DATETIME,
[Forms]![frmReports]![txtEndDate] DATETIME;
SELECT COUNT(*) AS CountOfPolicies
FROM tblMotor
WHERE Renewal(StartDate,
[Forms]![frmReports]![txtStartDate],
[Forms]![frmReports]![txtEndDate]);

Ken Sheridan
Stafford, England

Allen,

OK, that made a difference but does not do what I need. It is only
returning dates within the specified year, i.e. if I set the dates to
1/9/2007 to 30/9/2007 it returns dates within those two dates. What I
am trying to do is get it to return EVERY policy that has a StartDate
between 1/9/???? and 30/9/???? The actual year is irrelevant, if a
policy was started on 20/9/1994 it will next be due for renewal on
20/9/2008. So if I set the date range on the form to be 1/9/2008 to
30/9/2008 I want that policy to be included in the count along with
one that was started on 20/9/1999 and one started on 27/9/2007 and so
on. Does that clarify the problem more?

Peter Hibbs.

On Wed, 10 Sep 2008 23:27:36 +0800, "Allen Browne"

Sounds like Access is not interpreting your dates correctly.

1. In form design view, set the Format property of both text boxes to Short
Date so Access knows they are dates.

2. In query design view, choose Parameters on the Query menu.
Enter 2 rows in the dialog:
[Forms]![frmReports]![txtStartDate] Date/Time
[Forms]![frmReports]![txtEndDate] Date/Time

 

[Peter Hibbs]

Dale,

You are correct, there were two records with Null values. I have
modified the query to exclude those records and it works fine now.
Thanks very much for your help.

Peter Hibbs.

 

[Peter Hibbs]

Ken,

You were right, it does work correctly. The reason I got the error was
because there were a couple of records with Null in the StartDate
field. Thanks for your help, it has been very useful to me.

Peter Hibbs.

Peter:

I've tested it and the query runs OK, but I did find that I'd not taken
account of where the date range spans the end of the year and the policy date
comes before the start of the range. After debugging it I think this
amendment should cater for that scenario:

Public Function Renewal(dtmPolicyStart As Date, _
dtmRangeStart As Date, _
dtmRangeEnd As Date) As Boolean

Dim dtmDummyPolicyStart As Date
Dim dtmDummyRangeStart As Date
Dim dtmDummyRangeEnd As Date


' compute dummy dates
dtmDummyPolicyStart = _
DateSerial(2000, Month(dtmPolicyStart), Day(dtmPolicyStart))

dtmDummyRangeStart = _
DateSerial(2000, Month(dtmRangeStart), Day(dtmRangeStart))

dtmDummyRangeEnd = _
DateSerial(2000 + Year(dtmRangeEnd) - _
Year(dtmRangeStart), Month(dtmRangeEnd), _
Day(dtmRangeEnd))

' adjust for dates before start of range where range
' spans end of year
If Year(dtmDummyRangeEnd) > Year(dtmDummyRangeStart) Then
If dtmDummyPolicyStart < dtmDummyRangeStart Then
dtmDummyPolicyStart = DateAdd("yyyy", 1, dtmDummyPolicyStart)
End If
End If

' if policy started within range return True
If dtmDummyPolicyStart >= dtmDummyRangeStart _
And dtmDummyPolicyStart < _
DateAdd("d", 1, dtmDummyRangeEnd) Then

Renewal = True

End If

End Function

The only reason that springs to mind which might possibly account for the
error is if you've given the module the same name as the function. If so
then change the module name, using a suitable naming convention, to
mdlRenewal say. I'm doubtful that this is the cause, however, as I'd expect
an 'undefined function' error in that case, but I can't see any other
potential cause at first sight. Check the SQL of the query carefully and
make sure it exactly matches that which I posted.

Ken Sheridan
Stafford, England

Ken,

Tried it but get error message when I run the query.

"The expression is typed incorrectly or is too complex to evaluate.
For example, a numeric expression may contain too many complicated
elements. Try simplifying the expresson by assigning parts of the
expression to variables."

I will have another look later (just going to have some dinner) but I
think you are right, it is going to need a code function to process
the data.

If you have any thoughts please let me know.

Peter Hibbs.

The problem is that you need to cater for ranges which span the end of the
year. I'd suggest adding a function along the following lines to a standard
module , which computes dummy dates for the purpose of testing if a date is
within the range whatever the year:

Public Function Renewal(dtmPolicyStart As Date, _
dtmRangeStart As Date, _
dtmRangeEnd As Date) As Boolean

Dim dtmDummyPolicyStart As Date
Dim dtmDummyRangeStart As Date
Dim dtmDummyRangeEnd As Date


' compute dummy dates
dtmDummyPolicyStart = _
DateSerial(2000, Month(dtmPolicyStart), Day(dtmPolicyStart))

dtmDummyRangeStart = _
DateSerial(2000, Month(dtmRangeStart), Day(dtmRangeStart))

dtmDummyRangeEnd = _
DateSerial(2000 + Year(dtmRangeEnd) - _
Year(dtmRangeStart), Month(dtmRangeEnd), _
Day(dtmRangeEnd))

' if policy started within range return True
If dtmDummyPolicyStart >= dtmDummyRangeStart _
And dtmDummyPolicyStart < _
DateAdd("d", 1, dtmDummyRangeEnd) Then

Renewal = True

End If

End Function

You can then call it in a query like so:

PARAMETERS
[Forms]![frmReports]![txtStartDate] DATETIME,
[Forms]![frmReports]![txtEndDate] DATETIME;
SELECT COUNT(*) AS CountOfPolicies
FROM tblMotor
WHERE Renewal(StartDate,
[Forms]![frmReports]![txtStartDate],
[Forms]![frmReports]![txtEndDate]);

Ken Sheridan
Stafford, England

:

Allen,

OK, that made a difference but does not do what I need. It is only
returning dates within the specified year, i.e. if I set the dates to
1/9/2007 to 30/9/2007 it returns dates within those two dates. What I
am trying to do is get it to return EVERY policy that has a StartDate
between 1/9/???? and 30/9/???? The actual year is irrelevant, if a
policy was started on 20/9/1994 it will next be due for renewal on
20/9/2008. So if I set the date range on the form to be 1/9/2008 to
30/9/2008 I want that policy to be included in the count along with
one that was started on 20/9/1999 and one started on 27/9/2007 and so
on. Does that clarify the problem more?

Peter Hibbs.

On Wed, 10 Sep 2008 23:27:36 +0800, "Allen Browne"

Sounds like Access is not interpreting your dates correctly.

1. In form design view, set the Format property of both text boxes to Short
Date so Access knows they are dates.

2. In query design view, choose Parameters on the Query menu.
Enter 2 rows in the dialog:
[Forms]![frmReports]![txtStartDate] Date/Time
[Forms]![frmReports]![txtEndDate] Date/Time

 

[Allen Browne]

If you only put 10 numbers in your table, it's only going to work for 10
years from the origianal date, so 1994 won't work.

 

[Peter Hibbs]

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.

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.

 

[Dale Fye]

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.

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.