Discussion created by RobertWard on Oct 6, 2017
Latest reply on Oct 6, 2017 by beverly

I hope that someone can help me on this one.


I have a Program table which contains programs names and two number fields representing the last four digits of the year. The two number fields are parsed out from a global field, which contains a date range e.g. 2014-2015. One number field is YEARFROM and the other number field is YEARTO. In order to get the YEARFROM and YEARTO, I parse them out into global variables as $$_YEARFROM and $$_YEARTO, so that I ca use them in a find operation.


I am trying to write an ExecuteSQL statement which tells me if a particular program name exists within a date range like 2015 to 2016. Now I know that in the Program table I have two records that contain the program name "Secure Future$ Outreach" with a date of 2015 to 2016. Here is the SQL statement.


ExecuteSQL ("

SELECT Count(Name_Program)


WHERE Name_Program = 'Secure Future$ Outreach' and

' " & $$_YEARFROM & " ' = '2015' and

' " & $$_YEARTO & " ' = '2016' " ; "" ; "" )


The result I get is 0 when the result should return 2. Notice that I am including the two variables in the WHERE statement. I do not get a ?, so this is telling me that the syntax should be correct or at least it's acceptable. For now I am just trying to get the results based on hard coded dates, ultimately, I will change this to use a dynamic result. One thing to note is that the variable name $$_YEARFROM is a reserved word in SQL. But I don't know if that applies to variable names. In any case, I did try to double quote the variable name as $$_"YEARFROM" and I get an error doing that.


Any ideas from anyone on why I don't get my result of 2 returned?