Sum a Record based on a Date Field

[cantona]

Ive have a table that holds information about applications to courses. The table holds the year of the application and the date of the application in the following format;

Year Date
0607 21/05/2006
0708 28/07/2007
0607 04/03/2006

What I would like to do is produce a query so that I can compare the number of applications in one year against another. So far I have grouped my query by year and added a column to count the number of applications in the year. Giving me the following,

Year Total
0607 2000
0708 1340

This works fine but what I would also like to do is add a column showing how many applications were received at a particular point in time. For Example, by 1st April in each year, how many applications had been received? So far I’ve come up with the following, Ive added a further column to the report using this formula,

IIF([Application_Date] <= 01/04/2006,1,0) Ive set the property on the total row to Sum. This however, doesn’t seem to be working. The number produced is very low. When I check the dates, there should be lots before 1st April. What am I doing wrong???

 

[PHV]

I'd try this:
IIf([Application_Date] <= #2006-04-01#, 1, 0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cantona]

Hi PHV,

Thanks for that! ive now got it calculating a realistic figure!!