The following ExecuteSQL statement returns the correct count:

 

ExecuteSQL ( "

SELECT count (*) 

FROM "Class Member Data" 

WHERE ClassID = ? " 

; "" ; ""; 53)

 

But when I add some additional qualifying conditions like

 

ExecuteSQL ( "

SELECT count (*) 

FROM "Class Member Data" 

WHERE ClassID = ? and Region = ? " 

; "" ; ""; 53; 1)

 

it incorrectly returns 0.

 

What is the correct syntax?

 

Hi Doug,

What type of field (Text, Number, etc.) is "Region". You might need to wrap the 1 in quotes if it's a text field.

Region is an unstored calculation field, result number (but I had already tried quoting it without any difference).  The calculation is members::region, where members is a properly related table (and that's why the calc is unstored).  I also tried using members::region directly in the sql statement, without success.

I'm not seeing anything wrong with your syntax offhand, and the fact your query result is a zero and not a question mark tells me it's probably not syntax.

So, how many members that are in class 53 actually have a region 1 value? That is what you're looking to count in this select statement, right?

There are 3 records in which the Region value is 1. (I picked class 53 because it only has 26 records to make it easier to troubleshoot).

Solved.  The copy I was doing the testing on was old, and didn't have class 53 records. I was looking at the live db's values, but testing on the local copy.  D-OH!

Oh that's great news, man. Glad to hear you got it resolved b/c I was honestly stumped!

I see you've solved it, but I wanted to add that when I'm SQL troubleshooting, one of the first things to check is reserved words. I had tables named Language and Translation that tripped me up, for example. "Region" wouldn't have surprised me as reserved.