Expression Builder Expression Not Summing

[lhooker]

Can anyone see what is wrong with the below "Iif" statement in "Expression Builder" of a form "TextBox ? I'm trying to add all rows for the "Total" column in a table called "Reconciled", if the "CheckBox" is checked (i.e. Yes). Currently, I'm getting an incorrect value and if I check additional rows, the value does not changed.


=IIf([Reconciled],'Yes',Sum([Total]))

:banghead:

 

[JHB]

Below is a link to the description of the function Iif with some example, because the way you've set it up is wrong!
https://support.office.com/en-us/article/IIf-Function-32436ecf-c629-48a3-9900-647539c764e3

 

[Galaxiom]

As it is current written it means:

If Reconciled is ticked in the current record, put 'Yes' in the textbox otherwise Sum the Total field across the Form's Recordset. (Well it would if you had double quotes around 'yes'.)

You need to derive a field in the RecordSource query that holds the value you want to sum based on the state of the Reconciled checkbox in the record.

eg

Newfield: Iif([Reconciled], [Total], 0)

Then Sum that field in a textbox in the footer of the form.

= Sum(Newfield)

 

[lhooker]

JHB,

The examples did not help me with what I'm trying to do. Thanks ! ! !

 

[lhooker]

Galaxiom,

May I clarify your instructions (based on current structure) ? I will . . .


1. Created a column (i.e. 'Reconciled_Yes' in the table that is used in the
query for 'RecordSource' of the Form
2. In the TextBox "Control Source" enter
Reconciled_Yes: Iif([Reconciled], [Total], 0)
3. Create a Form footer
4. Create a new TextBox and enter =Sum(Reconciled_Yes) in the
"Control Source"

If I understood your instructions correctly, then this did not work. So, I know I misunderstood your instructions. Please show me what I'm misunderstanding. Thanks ! ! !

 

[lhooker]

Galaxiom/JHB,

I'm trying another solution. I created SQL code in VBA that checks if the "Reconciled" checkbox is checked, sums all found records. I then store the value is the TextBox. Thanks for your help ! ! !