>>I do something like this:
>>
>>
>>m.dStartDate={10/01/2000}
>>m.dEndDate={10/31/2000}
>>
>>SELECT prenotes.bankname, prenotes.portfolio, ;
>>SUM(IIF(!isnull(mid),1,0)) AS foundit, ;
>>SUM(IIF(isnull(mid),1,0)) AS notfound, ;
>> COUNT(prenotes.mid) AS tot_prnote ;
>> FROM prenotes ;
>> WHERE BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate) ;
>> GROUP BY prenotes.bankname ;
>> INTO CURSOR mis_prenotes
>>
>>
>>I get a record with bankname,foundit,notfound,tot_prnote.
>>
>>If there are more than one bank I get a record for each bank. If a bank is missing, I get nothing. I want to get a record for all the banks that are in the table regardless of there being a numbers for it or not. For example, one record would have bank A with numbers in the fileds and Bank B would have all zeroes in it. Is this possible?
>>
>>Thank you in advance for your help! :-)
>>randall
>
>
>
>You can do a UNION of your original query and one that finds all records that don't match your selection criteria. I think the following will also work although it may be a bear on a big table
>
>SELECT prenotes.bankname, prenotes.portfolio, ;
>SUM(IIF(BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate) AND !isnull(mid),1,0)) AS foundit, ;
>SUM(IIF(BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate) AND isnull(mid),1,0)) AS notfound, ;
>SUM(IIF(BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate),1,0)) AS tot_prnote ;
>FROM prenotes ;
>GROUP BY prenotes.bankname ;
>INTO CURSOR mis_prenotes

From the HELP:
The following field functions are available for use with a select item that is a field or an expression involving a field:

AVG(Select_Item), which averages a column of numeric data.


COUNT(Select_Item), which counts the number of select items in a column. COUNT(*) counts the number of rows in the query output.


MIN(Select_Item), which determines the smallest value of Select_Item in a column.


MAX(Select_Item), which determines the largest value of Select_Item in a column.


SUM(Select_Item), which totals a column of numeric data.
You cannot nest field functions

I'm not sure, that the syntax you showed is correct.