Can I Conditional Sum on a Form? (1 Viewer)

[PNGBill]

I have a Continuous Form which includes three Controls:

Principal - Currency
RefinanceAmount - Currency
APLBank - Text

On the footer or Header of the Form I would like two Unbound Controls.

One to Sum Principal Less RefinanceAmount where APLBank = BSP
The other to Sum Principal less RefinanceAmount where APLBank = ANZ

Can this be done and how?

By an unbound Control and IIF or by VBA??

 

[evanscamman]

Unbound control should work.

I need to make sure I understand what you want.

Is the Principal Less RefinanceAmount for a single record or for all records that match your criteria?

Are BSP and ANZ the only possible entries for APLbank? I think you may want to use a ComboBox instead of a text field here.

 

[PNGBill]

Thanks Evan, Managed to get it all working and have gone to town with calculations all over the form.

This form is a Daily Total form so the Combo Box is used in an earlier form where the actual task is performed and a bank selected. Here we just assemble the data and do Sums to arrive at values for GL input.

Will post my methods in a little while. Antivirus scan just kicked in and I will let it have it way for a while.

 

[PNGBill]

How to Conditional Sum on a Form.

In this example I have a Continuous Form with Bound and Unbound Controls (fields in Queries and Tables).

On the Form Footer and Header I have Unbound Controls that Sum the Controls in the Form Detail.

Example: Unbound Control in Form Footer or Header has this in the Control Source box of the Properties Data Tab.
=Sum([BoundControlName]) Substitute BoundControlName for your Control Name.

Where you have more then one of these Unbound Controls that Sum your Data, You can do calculations with these.
=[UnboundControlNameA]-[UnboundControlNameB] again, put in Control Source of your new Control (text box)
Note.. The UnboundControlNameA & B are the Name from your previous unbound controls. You get the Name from the Other Tab on your Control Property.

In example 1 we use the Data Source of the Bound Control but in example 2 we use the Name of the Control. When you create your Unbound Control (text box) the system gives it a name - Change the name to something meaningful But don't repeat a name already used by either a bound or unbound control.

Conditional Sum..

Example Bound Controls in your Form Details are:
SalePrice (Currency), Delivered (Text yes or No)
To Sum the Sale Price where Delivered is "Yes"

=Sum(IIf([Delivered]="Yes", [SalePrice],0))

again, in the Control Source of your Unbound Control's Data Property.

And should you wish to add or subtract these Controls, just refer to Their respective Control names.

Trust this assists