Hi all. Attempting to sum hours worked using SQL where a separate field matches a particular value in another field.

The calculation is determined from the "Tax Year" table.

The calculation references a child table where the job entries are created - Relationship is: Tax Year::YEAR = Job Lines::YEARid

ExecuteSQL (
"SELECT Sum (HRS TOTAL)
FROM \"Job Lines\"
WHERE IA = 'Y' 
YEARid = ? " ; "" ; "" ;
"" ; "" ; TAX YEAR::PrimaryKey 
)

I keep getting a ? as a result. My suspicions are somewhere in the last three lines of the calculation. Any ideas?

Edited February 1, 20233 yr by madman411

1. You need to quote both field names that contain a space;
2. You need to add AND between the two conditions of WHERE;
3. You need to remove the 2 redundant empty strings after the query:

ExecuteSQL (
"SELECT Sum (\"HRS TOTAL\")
FROM \"Job Lines\"
WHERE IA = 'Y' AND
YEARid = ?" ; 
"" ;  "" ; TAX YEAR::PrimaryKey 
)

 

Edited February 1, 20233 yr by comment