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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
Click to expand...

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]
 
Back
Top