Hi all

I am trying to utilize ExecuteSQL to calculate earnings per quarter last year, however it doesn't appear to be working and I'm sitting here scratching my head.

Table JOB LINES - where each check is entered and the check issue date.
Table JOB TRACKING - contains portal for JOB LINES where data for each tax year, including checks and the quarterly earnings fields.

Other calculation fields are able to reference each table accordingly and return accurate results for each tax year.

Let ( [

sqlQuery = "SELECT Sum (Chk_GROSS)
FROM \"JOB LINES\"
WHERE paymentFlag IS NULL 
AND
Chk_DATE BETWEEN ? AND ? 
AND
Form IN («FORMS») " ;

FORMS = Substitute ( JOB TRACKING::Quarter_Tax_Form ; ¶ ; "," )

] ;

Case ( 

ExecuteSQL ( 
Substitute ( sqlQuery ; "«FORMS»" ; JOB TRACKING::Quarter_Tax_Form ) ; "" ; "" ; 
JOB TRACKING::Quarter1Start ; JOB TRACKING::Quarter1End )  = "?" ; "" ; 

ExecuteSQL ( 
Substitute ( sqlQuery ; "«FORMS»" ; JOB TRACKING::Quarter_Tax_Form ) ; "" ; "" ; 
JOB TRACKING::Quarter1Start ; JOB TRACKING::Quarter1End )

)
)

Quarter1Start and Quarter1End contain the quarter dates, i.e. Jan 1 to March 31.
JOBTRACKING::Quarter_Tax_Form contains checkboxes from a value list to "filter" the particular tax form, i.e. W2, 1099, etc.

I realize I'm missing the link between the two tables (JOB LINES::YEARid = JOBTRACKING::PrimaryKey). I've tried including this and I'm still getting the same error (see below) so I'm trying to eliminate calculation steps to see where the error is coming from.

There is an error in the syntax of the query.

If I select a tax form (in this case "W2") in the checkbox set I get the following:

The column named "W2" does not exist in any table in the column reference's scope.

If the tax form I select includes a numerical form, such as 1099, I get the following:

An expression contains data types that cannot be compared.

Perhaps a more experienced user can see where I'm going wrong here.

Thanks so much.

This is very difficult to follow. I would suggest you reduce the query to the minimum and then add clauses and conditions one by one until you get an error. Then you will know what to focus on.

AFAICT, the last 2 error messages you describe are caused by data type mismatch. If the Form field is a Text field, then the values listed in the IN operator must be surrounded by single quotes - e.g.

Form IN ('W2', '1099')

(And of course any quotes and single quotes that might exist in the values themselves must be escaped.)

 

1 hour ago, madman411 said:

I am trying to utilize ExecuteSQL to calculate earnings per quarter last year,

Wouldn't it be simpler to produce a report summarized by quarter? Then you would get all 4 quarters at once. From what I see, you are actually trying to sum the earnings of the first quarter only. Even for that, a filtered portal might be a better suited alternative.