My query relates to the summing of monthly interest charges over the period of a financial year – the financial year being between July 1 and June 30.The problem I have is that at the change of financial year as happened recently the query gives a blank until the first interest charge is entered which may not be until August comes by. Instead of a blank I would like to display zero. Within the table as at July there is obviously no entry yet for either an interest charge or a date of the charge. I have tried both IsNull() and Nz() type functions in an endeavour to display a zero but it is not occurring. If anyone can advise what needs to be done much appreciated. Only some amateur Access experience here but more of a hacker.
Code:
SELECT IIf(IsNull([Val2]),"Interest Charged this Fin Yr","Interest Charged this Fin Yr") AS Text2, Nz(Sum([tbl_Interest].[MonthlyInterest]),0) AS Val2, tbl_Interest.AccountID
FROM qry_Account INNER JOIN tbl_Interest ON qry_Account.AccountID = tbl_Interest.AccountID
WHERE (((qry_Account.InUSe)="Yes") AND ((tbl_Interest.DateInterest)>=IIf(Month(Date())>6,DateSerial(Year(Date()),7,1),DateSerial(Year(Date())-1,7,1))))
GROUP BY tbl_Interest.AccountID, tbl_Interest.InterestType
HAVING (((tbl_Interest.InterestType)="Charged"));