Subform footer function

[NISMOJim]

I have a subform that displays dates and points (numbers) fields. I want the textbox in the form footer to display only the total points of the records that are 1 year or newer, however still showing all of the records in the detail section. The textbox control source is;



=Sum(IIf(Date()-[txtPtDate]<365,[Pts],0))

I have tried the syntax a couple different ways, but still get an error in the textbox in form view. I'm hoping it's something simple, but haven't figured it out yet. Can someone please help?

Thank you in advance.

[pbaldy]

If either the date or the points are calculated controls in the detail section, you can't sum it (I think...for sure the points, not for sure the date). Also make sure you're in the form footer, not the page footer.

[CJ_London]

instead of txtPtDate, use the name of the field (i.e. the controlsource of txtPtDate)

Also is Pts a calculated value in the form? e.g. Qty*0.01

if so you need to use that calculation instead of Pts in your sum

[NISMOJim]

Neither of the fields is calculated, both are stored in the table. And the textbox I'm working on is in the form footer, not the page footer.
The Record Source of the subform is

SELECT [qryPts].[PtDate], [qryPts].[PtPoints] FROM qryPts WHERE [qryPts]![PtPermit] = [Forms]![frmPermit]![Permit];

This part is working well in the detail section, as it is only showing records that match the permit number. But is it also the root of the problem since the record source is not a direct reference to a table/query? Or is the "Date()" part not understood in this use to represent todays date?

[June7]

Query for RecordSource should be just fine.
Use of Date() should also work.

Did you try suggestion to use field name instead of textbox name?

Field is PtPoints so what is Pts?

Could calculate in query: IIf(Date()-PTDate<365, [PtPoints], 0) AS NewPts

Then in textbox: =Sum(NewPts)

[ArviLaanemets]

In form Footer, there is is a functionality to display the total of any field in form's Detail section (grouping). To get total for last year only, instead table use a query as source, and add there a calculated field, which returns Qty for records of last year, and 0 otherwise (like SELECT *, Qty*Iif(Date()-PtDate > 365, 0, Qty) As LastYearQty FROM YourTable). In footer, you use grouping on this calculated field.

[NISMOJim]

Thanks June7, adding the field to the query did all the work for me. So simple, but out of my reach.