I have a report that is based on detail data in a table. In the OnOpen event of the report I am using the RecordSource property to change the recordsource using critieria chosen by the user on a form. The report sums up people by month. There is no data in the Detail section of the report, only in the date footer section. I need to create a running sum of the people that is the sum of the current month and the 5 previous months (that is, a 6-month ending sum). The runningsum function doesn't work because I only want to sum 6 records at a time and I want the sum to continue to move. I have tried the dsum function but I can't seem to get the criteria to work. I have tried:
DSum([txtActive],"mytablename",[txtdate] >= dateadd("m",-5,[Tabledate]) )
where [txtActive] is the name of the control on the report I am using to sum the people and [txdate] is the control on the report with the date in it and [tabledate] is the control source for [txtdate]. I think the problem comes because I am using the recordsource property to change it on open of the report. I tried using dsum in a calculated control on the report and also in the onformat even of the date footer. When I put it in the date footer it can't find [tabledate] and so I get an error.