I am having a problem with a UDF call from SQL SELECT. It is painfully slow in the form but the UDF has a fairly complicated dependency on dates and # of active child records based upon these dates.

The best way to overcome the speed problem, I have discovered, is to get rid of the UDF call and replace the logic with IIF()s and other native Foxpro functions imbedded in SQL statements.

Problem: In order for this to work I have to be able to do a conditional count of records grouped by parent id and the COUNT(field_name) SQL function isn't working properly even with the correct GROUP BY and HAVING clauses.

Question: Is there a native Foxpro function that anyone out there knows about that returns the # of records which meet a conditional parameter, kind of like:

COUNT(Id, startdate >= DATE() AND enddate <= DATE()) AS numberactive,

or maybe a trick you can play with the CALCULATE or COUNT TO commands to get SQL to interpret the AS clause correctly.

Help, Anyone....