If your data looked like this:
What date do you want returned?
or both dates with a "record separator" separating them?
For this particular situation, there is very little chance of duplicate values. However, if there was an exact duplicate value, I probably would want the earliest date for a min, and the latest date for a max.
At this stage, a list, since executesql returns rows, would be the best answer. Then the data can be analyzed based on the situation. It has to be analyzed anyway, since sql dates are different than fm dates. So I'd be happy with getting all results that was minimum along with their dates. I can apply any solution to include other fields later on.
Thanks for your reply
I was able to get the correct results using this calculation:
Let ( M = ExecuteSQL ( "SELECT Min ( TheValue ) FROM ExecuteSQLmatchingMinDemo" ; "" ; "" ) ;
ExecuteSQL ( "SELECT Thedate FROM ExecuteSQLmatchingMinDemo WHERE TheValue = ?" ; "" ; ¶ ; M )
You can download a copy of the file where I tested this here: https://dl.dropbox.com/u/78737945/ExecuteSQLmatchingMinDemo.fmp12
Thanks for all your work. It's funny, as this is what I have in my second sample. The 'M=E Used' part returns a number with about 16 decimals, but still, if it finds this record in the first sql statement, I'm baffled as to why the second sql statement can't find this same record to return the date.
By adding a ridulously small number to M and using '<=' in the 2nd sql, stead of just '=', I get the results I was expecting.
Could it be that sql is modifying M because of all the decimal places, maybe rounding that 16th place, so it is no longer equal?
Thanks again for you time
Try using exactly what I have. What you are using is slightly different.
Tried your demo. I converted your num field to a calculation ( which mine was ) and it still worked. When I make this field unstored (which mine was) then it doesn't work as expected. So the field has to be stored.