Need report help

[vs2k8]

Hello guys,

My report is based on below query:

SELECT Log_Tbl.DocType, Log_Tbl.DocGroup, Count(Nz([Log_Tbl].[PName],1)) AS TotPname, Count([EDes_Tbl.ELogType] & [EDes_Tbl.ELogName]) AS ECounts
FROM Log_Tbl LEFT JOIN EDes_Tbl ON Log_Tbl.IdNum = EDes_Tbl.IdNum
GROUP BY Log_Tbl.DocType, Log_Tbl.DocGroup
HAVING (((Log_Tbl.DocType) Is Not Null));

User wants to run this report every quarterly(Q1: 01-Jan-08 to 31-Mar-08,Q2 :01-Apr-08 to 30-Jun-08 Q3:01-Jul-08 to 30-Sep-08 and Q4: 01-Oct-08 to 31-Dec-08.) and yearly too.Log_Tbl.Log_Dt is the date to be queried.
Now i want when I hit the report button it will ask user to enter the parameter for Q1,Q2,Q3,Q4,yearly and I get results for that particular quarter.

So can anyone help me how to achieve this , how I should proceed.

Thanks in advance.

-V

[NeoPa]

I've tidied up the SQL for display (and easy understand) purposes, yet I cannot see anything here which can relate to dates. I'm completely at a loss as to what you're hoping to achieve.

SELECT Log_Tbl.DocType,
       Log_Tbl.DocGroup,
       Count(Nz([Log_Tbl].[PName],1)) AS TotPname,
       Count([EDes_Tbl.ELogType] & [EDes_Tbl.ELogName]) AS ECounts
 
FROM Log_Tbl LEFT JOIN EDes_Tbl
  ON Log_Tbl.IdNum = EDes_Tbl.IdNum
 
WHERE (Log_Tbl.DocType Is Not Null)
 
GROUP BY Log_Tbl.DocType,
         Log_Tbl.DocGroup

[vs2k8]

Thanks for your reply and making query look better, sorry that you can't get my question,

SELECT Log_Tbl.DocType,
       Log_Tbl.DocGroup,
       Count(Nz([Log_Tbl].[PName],1)) AS TotPname,
       Count([EDes_Tbl.ELogType] & [EDes_Tbl.ELogName]) AS ECounts
 
FROM Log_Tbl LEFT JOIN EDes_Tbl
  ON Log_Tbl.IdNum = EDes_Tbl.IdNum
 
WHERE ((Log_Tbl.DocType Is Not Null)
  AND (Log_Tbl.Log_Dt Between [From_Date] and [To_Date]))
 
GROUP BY Log_Tbl.DocType,
         Log_Tbl.DocGroup

I added in the where clause a code as you can see above, but instead of passing date in From_Date & To_Date
** Admin Edit ** All code added in [ CODE ] tags as this is a requirement AND it's impossible to make much sense of otherwise ** /Admin Edit **
is there any way user enters Q1 or Q2 or Q3 or Q4 and year and he gets report for that quarter and year
for e.g if he enters Q1 and 2008 report will run for 01-Jan-2008 to 31-Mar-2008.

Hope this will clear the picture what I want to achieve.

Thanks in advance.

-V

[NeoPa]

Well, doctoring the SQL would be easy enough, but as you say the report has to run from this (I'm assuming the SQL is in a QueryDef object - or saved query) then I would suggest that applying a filter to the report when it is opened (from code in your form obviously) would be the way to go. As the filter would have to be applied to the field [Log_Dt], this field must necessarily be available as a SELECTed field in the QueryDef (so will need to be added - even if it is not used elsewhere in the report).

Does this sound like a solution?

[vs2k8]

Thanks for your solution.

-V

[NeoPa]

Pleased to hear it suits.

Let us know if you need further help implementing it.