By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Need report help

vs2k8
P: 13
Hello guys,

My report is based on below query:
Expand|Select|Wrap|Line Numbers
  1. SELECT Log_Tbl.DocType, Log_Tbl.DocGroup, Count(Nz([Log_Tbl].[PName],1)) AS TotPname, Count([EDes_Tbl.ELogType] & [EDes_Tbl.ELogName]) AS ECounts
  2. FROM Log_Tbl LEFT JOIN EDes_Tbl ON Log_Tbl.IdNum = EDes_Tbl.IdNum
  3. GROUP BY Log_Tbl.DocType, Log_Tbl.DocGroup
  4. 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
Aug 6 '08 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT Log_Tbl.DocType,
  2.        Log_Tbl.DocGroup,
  3.        Count(Nz([Log_Tbl].[PName],1)) AS TotPname,
  4.        Count([EDes_Tbl.ELogType] & [EDes_Tbl.ELogName]) AS ECounts
  5.  
  6. FROM Log_Tbl LEFT JOIN EDes_Tbl
  7.   ON Log_Tbl.IdNum = EDes_Tbl.IdNum
  8.  
  9. WHERE (Log_Tbl.DocType Is Not Null)
  10.  
  11. GROUP BY Log_Tbl.DocType,
  12.          Log_Tbl.DocGroup
Aug 6 '08 #2

vs2k8
P: 13
Thanks for your reply and making query look better, sorry that you can't get my question,
Expand|Select|Wrap|Line Numbers
  1. SELECT Log_Tbl.DocType,
  2.        Log_Tbl.DocGroup,
  3.        Count(Nz([Log_Tbl].[PName],1)) AS TotPname,
  4.        Count([EDes_Tbl.ELogType] & [EDes_Tbl.ELogName]) AS ECounts
  5.  
  6. FROM Log_Tbl LEFT JOIN EDes_Tbl
  7.   ON Log_Tbl.IdNum = EDes_Tbl.IdNum
  8.  
  9. WHERE ((Log_Tbl.DocType Is Not Null)
  10.   AND (Log_Tbl.Log_Dt Between [From_Date] and [To_Date]))
  11.  
  12. GROUP BY Log_Tbl.DocType,
  13.          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
Aug 6 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
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?
Aug 6 '08 #4

vs2k8
P: 13
Thanks for your solution.

-V
Aug 6 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
Pleased to hear it suits.

Let us know if you need further help implementing it.
Aug 6 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.