Sum If in footer (1 Viewer)

[mtagliaferri]

I have a form in datasheet view with multiple entries, one column named 'amount' containing amount and the other column named 'Type' containing a single letter (P as Payment and D as Deduction) the datasheet can contain payments and deductions. I need to place two unbound text boxes in the footer of the form one will have the sum of all payments and the other the sum of the deductions.
I am struggling with the formula in the Control Source, what should I have to Sum If Type equals to D or P
Thanks

 

[CJ_London]

try this for payment

=sum(iif([type]="P",[Amount], 0))

Note that Type is a reserved word, using it as a field name can cause unexpected and misleading errors. Strongly recommend changing it to something meaningful within the context of the app, not just the table (e.g. AmtType)

I also note your example has an unspecified Type

 

[bob fitz]

Form footer doesn't show in datasheet form. You would need to use a Continuous form or put your Datasheet form on a new form (as a subform) and then have two text boxes on the new main form that reference the two text boxes that you have on the subform (datasheet)

 

[CJ_London]

you just have a form - which has a header and footer - but set the default view to datasheet - controls in the header and footer will still 'work'

 

[bob fitz]

you just have a form - which has a header and footer - but set the default view to datasheet - controls in the header and footer will still 'work'

But I don't think they are visible because the header and footer aren't visible on a datasheet form.

 

[CJ_London]

no - but on the main form you have a control to reference the subform total control - see this link for basically the same question

sum multi record price

=sum([price_out]) give error how to sum all parts price in subform

www.access-programmers.co.uk

 

[bob fitz]

no - but on the main form you have a control to reference the subform total control - see this link for basically the same question

sum multi record price

=sum([price_out]) give error how to sum all parts price in subform

www.access-programmers.co.uk

I agree but isn't that what I said in post #3

 

[CJ_London]

I was interpreting this

Form footer doesn't show in datasheet form.

It does in design view so can be used - think just a different way of saying the same thing

 

[mtagliaferri]

try this for payment

=sum(iif([type]="P",[Amount], 0))

Note that Type is a reserved word, using it as a field name can cause unexpected and misleading errors. Strongly recommend changing it to something meaningful within the context of the app, not just the table (e.g. AmtType)

I also note your example has an unspecified Type

Thanks that seems to do the trick, thanks for the hint with the 'Type' field will change the name.