3 Replies Latest reply on Dec 7, 2015 5:21 PM by user19752

    ExecuteSQL issue with a Year value extracted from a date

    Stu412

      Hi there

       

      I'm using some SQL to extract average values by 'type' and by 'year'.  Every row of data held on my table has a date on it, representing the year it comes from, and due to the data source, this is always a full date, such as 30/06/2015 for June 30th etc. 

       

      All I need is the year itself, so have created a field called Year and entered 'Right(Date,4)' to produce 2015 from the example above.  This is what breaks my SQL which I am looking to place on a sub summary layout to get analysed data by row/sales type.

       

      If I use the code below, it works fine because it's not referencing the Year field I created.  However, I need the year breakdown, so this looks pretty, but isn't detailed enough :

       

      ExecuteSQL ("

       

      Select AVG(Sales)from Sales_Home where

       

      Region = ? and

      SalesType = ?

       

      ";"";"";

       

      Customer_Home::Region;

      SalesType

       

      )

       

       

      If I then look to include the year in the criteria, I get the syntax error '?' result and I'm not sure why it would do this, only that the SQL doesn't like something upstream in the initial Year calculation:

       

      ExecuteSQL ("

       

      Select AVG(Sales)from Sales_Home where

       

      Region = ? and

      SalesType = ? and

      Year = ?

       

      ";"";"";

       

      Customer_Home::Region;

      SalesType;

      Year

       

      )