ExecuteSQL with variable

[jimlongo]

Hi, I just started using this function, it's very helpful.

 

I have a calculation that returns some transactions for this year.

 

 

ExecuteSQL ( "SELECT n_transactionAmountGross , t_transactionModel , t_FirstName frmt  FROM Transactions  WHERE  FamilyID_CustomerID = ?  AND c_transDate_frmt = 2013  " ; " : " ; "" ; FamilyID )
)

I'd like to substitute the current year for the hardcoded year by using a variable, however this doesn't work.

Let ( [   _yr = GetAsNumber (Year(Get(CurrentDate))  ) ] ; 

ExecuteSQL ( "SELECT n_transactionAmountGross , t_transactionModel , t_FirstName frmt  FROM Transactions  WHERE  FamilyID_CustomerID = ?  AND c_transDate_frmt = _yr  " ; " : " ; "" ; FamilyID )
)

What simple thing am I missing here?

 

 

 

Edit:

 

came across the pinned topic that speaks to tables with underscores in the name and tried the suggestion from there to no avail . . . as well as just removing the underscore from the variable name, and also then trying to quote the variable. 

 

Let ( [   _yr = GetAsNumber (Year(Get(CurrentDate))  ) ] ; 

ExecuteSQL ( "SELECT n_transactionAmountGross , t_transactionModel , t_FirstName frmt  FROM Transactions  WHERE  FamilyID_CustomerID = ?  AND c_transDate_frmt = "  & Quote("_yr")  &   "" ;    " : "   ;   ""     ;    FamilyID )
)

 

Thanks,

jim

[eos]

Add the name of the variable to the parameter list, then inside the SQL statement refer to it with the ? placeholder; FM will resolve the variable and insert the value into the SQL, like it does with the reference to the FamilyID field. Here's a pretty-printed version:

 

Let (

myYear = Year ( Get ( CurrentDate ) ) ;

 

ExecuteSQL

( "

SELECT n_transactionAmountGross , t_transactionModel , "t_FirstName frmt"  // there's a blank in the field name

FROM Transactions

WHERE

  FamilyID_CustomerID = ? AND

  c_transDate_frmt = ?

" ;    " : "   ;   ""  ;

FamilyID ; myYear

)

 

)

 

[jimlongo]

Thanks for that eos, putting it as a parameter worked.

 

Just to note, the underscore still causes a problem, and enclosing the field name with the space breaks it.  Here's what eventually worked for me.

Let (  
	yr =  Year(Get(CurrentDate)) ; 

ExecuteSQL (" 
	SELECT n_transactionAmountGross , t_transactionModel , t_LastName frmt 
	FROM Transactions  
	WHERE  FamilyID_CustomerID = ?  AND c_transDate_frmt = ? 
        "   ;    " : "   ;   ""  ;  FamilyID; yr  )

)