Microsoft Access (Report - Expression Builder): Sum iif values, dependent on different field that is null. Sum iif numbers that depend on null value fields

🖶 PRINT

Legend: Access Functionalities/Expression Builder/Codes [Field names in my database]

Tags: Reports Expression Builder Design View Sum IIf Null Values 

Problem

  At the end of my Report (Footer), I have a Load Count Summary that calculates the Sum of Regular Loads, Recycle Loads, and Other Loads. For my Report, I know that Regular Loads are the Sum of records that have the either [Material] as Garbage or no material listed (i.e. 3rd and last record shown below). On the Form that reflects this Report, if NO [Material] is entered, it appears in the Table and Report as a Null value.

Fig1: Loads Report in Report View. [Materials] outlined in red & [Number of loads] outlined in blue

The [Location Name] is the same for all of these records(1) (shown above) so the [Materials] field (outlined in red) is the only field that differentiates each record from one another; and I want to calculate the Sum of the [Number of Loads] (outlined in green) (shown below).

Under my date footer (for monthly calculations) I have a table for the Monthly Load Summary broken up into 3 parts: Regular Loads, Recycle, and Other. 

To calculate the Sum of the Recycle and Other Loads are fairly simple! Using the Expression Builder on the fields, we will be using two main functions: Sum and IIf. 

Sum(expression)

IIf(expression, truepart, falsepart)

The following statement is IF the [Materials] field is "Recycle" (or for the other field, "Other") then Sum the [Number of loads]. If the statement is False, then the value will be 0 (zero) otherwise. 

=Sum(IIf([Materials]="Recycle",[number_of_loads],0))

=Sum(IIf([Materials]="Other",[number_of_loads],0))

For Regular Loads, I want to calculate the Sum of the [Number of loads] for both "Garbage" [Materials] and [Materials] that were left blank in the record. 

Following the same format as the expressions for Recycle and Other Loads, we know for Garbage:

=Sum(IIf([Materials]="Garbage",[number_of_loads],0))

And for [Materials] that were left blank, those are Null values; and this is how it's built in the Expression Builder.

=Sum(IIf(IsNull([Materials]),[number_of_loads],0))

Then to include them in the same text box, write them in their own separate Sum(IIf()) statements and ADD them together all in the expression builder

= Sum(IIf([Materials]="Garbage",[number_of_loads],0)) + Sum(IIf(IsNull([Materials]),[number_of_loads],0)) 

Footnotes

(1) Location is the same for all records because we have a location that looks similar to the one that's exclusive to this report, so I made sure to include the location that way when we're checking the records, we're making sure that we entered the correct location.

Let me know if you need any clarification on anything or if any information is incorrect! I'm updating this blog as I learn and I just started MS-Access a little over a year ago! :)

Color coded plenty for my neurodivergent buddies