Stu412

ExecuteSQL issue with a Year value extracted from a date

Discussion created by Stu412 on Dec 7, 2015
Latest reply on Dec 7, 2015 by user19752

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

 

)

Outcomes