I have a calculation that uses the ExecuteSQL function:
ExecuteSQL ( "SELECT COUNT(ControlNumber)
LEFT OUTER JOIN Manufacturer ON ComplaintItems.SpplCde = Manufacturer.ManufacturerCode
WHERE ComplaintQuarter_aec = 4
AND ComplaintYear_aec = Manufacturer.link_ThisYear_aecg
AND BrandedOrPrivateLabel_aec = 'P'
and flag_void_aec <> 1
AND flag_International_aec <> 1
and SpplCde = ?" ;
"" ; "" ; Manufacturer::ManufacturerCode )
In the Data Viewer, everything works correctly, and I obtain the values I expect when I'm in the "Manufacturer" table and switch from one record to another.
When I use this same calculation in a calculated field in the Manufacturer table, I receive "?" as the result. (I did set the calculation to return Text, since that's what the ExecuteSQL function returns, even though the value is numerical, but that made no difference.
I just noticed that all of my "ANDs" are not upper-case, but it works in the data viewer. I will fix that to see if that makes a difference in the calculated field.
Running version 16 on client and server.
Does anyone see what I'm missing?