I am using the following SELECT to get deom data I need.


SELECT collections.settdate, collections.bankname, collections.portfolio, MONTH(collections.settdate) AS mymonth, ;
SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) AND (trancode = cCredit1 OR trancode =cCredit2 OR trancode =cCredit3), 1 , 0)) AS credits , ;
SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) AND (trancode = cCredit1 OR trancode =cCredit2 OR trancode =cCredit3), collections.amount ,0.00)) AS credit_amt, ;
SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) AND (trancode = cDebit1 OR trancode =cDebit2 OR trancode =cDebit3), 1 , 0)) AS debits , ;
SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) AND (trancode = cDebit1 OR trancode =cDebit2 OR trancode =cDebit3), collections.amount ,0.00)) AS debit_amt, ;
SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) ,1,0)) AS tot_num, ;
SUM(IIF(BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) ,collections.amount ,0.00)) AS tot_amount ;
FROM collections ;
WHERE BETWEEN(collections.settdate, m.dStartDate, m.dEndDate) and collections.portfolio= cBank ;
GROUP BY collections.settdate, collections.bankname ;
INTO TABLE eraseme2


If one fo my banks is not in the specified date period, I get nothing which i think is right. I want to get all zeroes for a bank not in the date sequence for example, FUNB is missing and I get FUNB credits "0"

Is there a simple way to do this?

thanks in advance for your help.

Randall