tried that -thru another query (not sure how to filter in a report??)
basically i have transactions with a [datee] date field. I wish to group them by financial year. I first used this expression to determine the year (which ends jun 30)
fyear: (Year([datee])-IIf([datee]<DateSerial(Year([datee]),7,1),1,0)) BUT I don't want the records which do not have a complete year to june of this year so I did the same for date() to measure the date today. then I subtracted the two and I wanted to specify a criteria in that field to show only if its more than 0 because that would get rid of the last year which is not yet up to jun 30
this then is my sql
SELECT Expenses.ExpenseID, Expenses.AmountSpent, Expenses.Date AS datee, [tblExpensesCategories].ExpensesCategoryID AS Expr1, Expenses.EProperty, ExpenseTypes.Name, Properties.Property AS prop, (Year([datee])-IIf([datee]<DateSerial(Year([datee]),7,1),1,0)) AS fyear, Year(Date())-IIf(Date()<DateSerial(Year(Date()),7,1),1,0) AS fyearNow, IIf([fyearnow]-[fyear]>0,True,False) AS complete
FROM tblExpensesCategories, Properties INNER JOIN (ExpenseTypes INNER JOIN Expenses ON ExpenseTypes.ID = Expenses.ExpenseType) ON Properties.PID = Expenses.EProperty
WHERE (((Expenses.Date) Between [forms]![frmexpenses]![fromtext] And [forms]![frmexpenses]![totext]) AND ((Expenses.EProperty) Like [forms]![frmexpenses]![propcombo]) AND ((IIf([fyearnow]-[fyear]>0,True,False))=True) AND (([tblExpensesCategories].[ExpensesCategoryID]) Like [forms]![frmexpenses]![expcombo]))
ORDER BY ExpenseTypes.Name, Expenses.Date;
hope i have explained myself
thanx