Quite simply can anyone point out what is wrong with this?

Let ([

~begin = Reports » Selector::gDateBegin ;
~end = Reports » Selector::gDateEnd ;
~type = Reports » Selector::gPaymentType

];

ExecuteSQL (

"
SELECT SUM ( n.commission )

FROM Commission n

INNER JOIN Payments p
ON p._pk_payment_id = n._fk_payment

WHERE p.date >= ? AND p.date <= ? AND n.commissionType = ?
"

;"";""; ~begin ; ~end ; "Sales"
)

)

Just trying to pull the total Sales commissions for a date range.

Thanks

 

Never mind. I got it. Just had to do some quoting...

Let ([

~begin = Reports » Selector::gDateBegin ;
~end = Reports » Selector::gDateEnd ;
~type = Reports » Selector::gPaymentType

];

ExecuteSQL (

"
SELECT SUM ( n.commission )

FROM Commission n

INNER JOIN Payments p
ON p.\"_pk_payment_id\" = n.\"_fk_payment\"

WHERE p.\"date\" >= ? AND p.\"date\" <= ? AND n.commissionType = ?
"

;"";""; ~begin ; ~end ; "Sales"
)

)

Edited October 19, 201510 yr by Ron Cates

 

Cheers! you found a common mistake: use of keyword/reserved word in the name of a table or field (column). Consider renaming your 'date' field to something like 'paymentDate'. You'll be glad later. 

Yep, and fields that begin with an underscore was the other issue. Good job, Ron.