>>>>>I can add a count subtotal to my report for a certain field, but I need a count of the records where the field equals a certain criteria, and then another count of the same field where the field equals another criteria.
>>>>>
>>>>>Any advice on how I can do this?
>>>>>
>>>>>Thanks!
>>>>>
>>>>>Jeff Rusch
>>>>>UW-Madison
>>>>
>>>>1. Report from cursor
>>>>2. When you create cursor add two fields like:
>>>>SELECT field1, field2, .......
>>>> IIF(table.field1=nCrit1,1,0) AS CritField1, ;
>>>> IIF(table.field1=nCrit2,1,0) AS CritField2, ;
>>>> .......
>>>>3. Count SUM() for CritField1, CritField2 as any other report subtotals.
>>>
>>>Thanks Edward! That works perfectly, but I found I also need a count of records that don't match either criteria. So I tried: IIF(ALLTRIM(famsingl)"F" OR ALLTRIM(famsingl)"S",1,0) AS fblank but this is always returning 1, and I tried IIF(ALLTRIM(famsingl)"F",IIF(ALLTRIM(famsingl)"S",1,0),0) AS fblank but this always returns 0.
>>>
>>>Ideas?
>>>
>>>Thanks again!
>>>
>>>Jeff
>>
>>IIF(ALLTRIM(famsingl)"F" AND ALLTRIM(famsingl)"S",1,0) AS fblank
>
>After I posted my last message, I tried changing the OR to an AND but it didn't appear to work. In my test table all records had an S or an F except for one. It was blank. So I tried: IIF(ALLTRIM(famsingl)!="F" AND ALLTRIM(famsingl)!="S" ;
> OR ISBLANK(famsingl),1,0) AS fblank
>This appears to work. But now I wonder why I needed to add the ISBLANK, shouldn't the expression not equal to F and not equal to S include the blank record?
I don't know. It should work OK without ISBLANK(), but if it works with ISBLANK() so use it (take phylosophical approach).
Edward Pikman
Independent Consultant