Query - specifying between dates with set outcome

[dinicholls]

Hi,

My mind seems to have wandered into the ether on this one!!! I am trying to set up a formula field to show that if a date is in a certain range, bring back result 'a'.

'Year of enrol' formula is as follows:

if {results.Start Date} = #01/08/07# to #31/07/08# then '07 - 08' else
if {results.Start Date} = #01/08/08# to #31/07/09# then '08 - 09' else
if {results.Start Date} = #01/08/09# to #31/07/10# then '09 - 10' else 'ERROR'

The results it returns on the report:

Year of Enrol Results start date
ERROR 28/04/09
ERROR 05/05/09
ERROR 25/04/09
09 - 10 05/09/08

Have I truly gone loop the loop, or am I missing something SO simple I should be shot?!

ANY help would be VASTLY apppreciated

 

[lbass]

I think the literals are being misinterpreted. Try:

if {results.Start Date} in date(2007,8,1) to date(2008,7,31) then
'07 - 08' else
if {results.Start Date} in date(2008,8,1) to date(2009,7,31) then
'08 - 09'else
if {results.Start Date} in date(2009,8,1) to date(2010,7,31)then
'09 - 10' else
'ERROR'

-LB