Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    52

    Red face Unanswered: [Date by Month] problem urgent

    i m trying to get report on month bases like March-1997 to june-2001, but query gives me result of all the records of march and june.
    i generated it through Access.
    the query is

    SELECT DISTINCTROW Format$([Kotri].[Date],"mmmm yyyy") AS [Date by month], Avg(Kotri.Downstream) AS [Avg Of Downstream], Avg(Kotri.Upstream) AS [Avg Of Upstream] FROM Kotri GROUP BY Format$([Kotri].[Date],"mmmm yyyy"), Year([Kotri].[Date])*12+DatePart("m",[Kotri].[Date])-1 HAVING (((Format$([Kotri].[Date],"mmmm yyyy")) Between "MARCH 1997" And "JUNE 2001"));
    Last edited by waqas; 01-22-03 at 06:43.

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: [Date by Month] problem urgent

    Originally posted by waqas
    i m trying to get report on month bases like March-1997 to june-2001, but query gives me result of all the records of march and june.
    i generated it through Access.
    the query is

    SELECT DISTINCTROW Format$([Kotri].[Date],"mmmm yyyy") AS [Date by month], Avg(Kotri.Downstream) AS [Avg Of Downstream], Avg(Kotri.Upstream) AS [Avg Of Upstream] FROM Kotri GROUP BY Format$([Kotri].[Date],"mmmm yyyy"), Year([Kotri].[Date])*12+DatePart("m",[Kotri].[Date])-1 HAVING (((Format$([Kotri].[Date],"mmmm yyyy")) Between "MARCH 1997" And "JUNE 2001"));
    Probably a problem due to the HAVING using a STRING to compare instead of a DATE. Try this:

    SELECT DISTINCTROW Format$([Kotri].[Date],"mmmm yyyy") AS [Date by month], Avg(Kotri.Downstream) AS [Avg Of Downstream], Avg(Kotri.Upstream) AS [Avg Of Upstream] FROM Kotri
    WHERE [Kotri].[Date] Between #03/01/1997# AND #06/30/1997#
    GROUP BY Format$([Kotri].[Date],"mmmm yyyy"), Year([Kotri].[Date])*12+DatePart("m",[Kotri].[Date])-1;

  3. #3
    Join Date
    Jan 2003
    Posts
    52

    Re: [Date by Month] problem urgent

    man u r genius but how did ya do it, i don't understand, i mean if we follow the format it should work?? like it says mm and yyyyy and

    plus i want to get the reports for 5 days or 7 days interval with same data, can u plz help me abt it
    Last edited by waqas; 01-22-03 at 12:20.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •