Simple query but difficult for each end of month between date rang

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter - 08 Mar 2006 07:09 GMT

I have a table that contains service calls. I want to show the number of
service calls that were still open at the end of each month. e.g Jan 06 - 2,
Feb 06 - 3
I have a query :
SELECT Count(*) AS Expr1
FROM tbl
WHERE ((tbl.EntryDate) Is Not Null And (dbo_tbl.EntryDate)<=[problem value])

This is only good if I type the problem value in. But I need to automate it
so that the report returns data for date range gathered from a form. I can
see that I need to determine the end of each month between the date range,
and run the query for each of those dates. Is this possible in a query ...
or is this better done in a function and then somehow linking this function
to the report.

Reply to this Message

John Vinson - 08 Mar 2006 20:58 GMT

>I have a table that contains service calls. I want to show the number of
>service calls that were still open at the end of each month. e.g Jan 06 - 2,
[quoted text clipped - 10 lines]
>or is this better done in a function and then somehow linking this function
>to the report.

You need to call a (builtin) function from the Query:

WHERE ((tbl.EntryDate) Is Not Null And (dbo_tbl.EntryDate) <
DateSerial(Year([EntryDate]), Month([EntryDate] + 1, 1))

John W. Vinson[MVP]

Reply to this Message