Sum If Not Null in Report

[QueenCashew]

I am trying to sum a Group in a report if the values in the report are not null. We are trying to track inventory as it moves through the shipping phases and have information that is provided to us in spreadsheets, if the event hasn't yet happened the spot if left blank. I am trying to sum the dollar amount of the inventory at the origin, port, rail, ect. if there is a date in one of the columns. These dates are sorted by Week.
The "Total" column is the money column.
The "Port DptWk" is the Week that the merchandise departed the port. If it is Null it has not had that event occur yet.
These are all sorted according to the Week that they are estimated to arrive at our facility. So I am trying to sum just that weeks merchandise that is at the port, on the rail, ect. in the subgroup for that week.

I have tried

=Sum(IIF([Port DprtWK],<>Null))([Total])
=Sum(IIF([Port DprtWk]),Is NotNull)([Total])

I am at a loss. It will sum all the containers for the week in that subgroup, but I can't get it to exclude Nulls. I am just smart enough to know when I don't know what I am doing so any help would be appreciated.

 

[GinaWhipp]

Hmm, why not just use Nz(), i.e. Nz(Port DprtWK],0), then just sum on that. Actually might be better to use the NZ() in the Recordsouce, easier to Sum on.

 

[QueenCashew]

Doesn't Nz return zeros? That is not what I am wanting.

The data that I am looking at if it is null will eventually become a date, but I don't want a total for the stuff that is blank right now.

I was also thinking maybe I am trying to use expression in a different order then they need to be, like =Sum(iif([Total], [Port DprtDt]Is Not Null))

I included the parts of the report I currently have working, the column that say a week number are what I am wanting a monetary total of, if they are blank they haven't reached that point at that week so there is no value to them, but there maybe next week.

 

[plog]

Not to get all mathy on you but, 0 is what mathematicians call the identity element (https://en.wikipedia.org/wiki/Identity_element) of addition. Use it and it has no effect on that operation.

Sum a million of them and the number 2 and you only get 2. Converting nulls to 0's will have no effect on the sum.

 

[QueenCashew]

Yes but in this instance a Null isn't really a Zero. if there is a Zero in the Date column that material still has a monetary value, and that $dollar number would affect the total sum of port merchandise if included in a position that is blank.

 

[plog]

You're losing me, and I think yourself.

You initially said you had 2 fields that went into a summation--a Date field whose value was to be tested and a Total field whose value was to be used . If the Date field was null then that Total value shouldn't be included in the Sum. Replacing 0 for that Total value accomplishes that.

Now you are talking about Summing date fields? Why would you ever do that? I mean what's 2/7/2016 + 3/11/2000 + 5/31/1988? Further if a 0 is in a date field it represents a date prior to 1900, does your data go that far back?

Gina's mehtod was to test the Date field and then either add the Total field to the Sum (If Date Not Null) or add 0 to the Sum (If Date Is Null).

Perhaps its time for sample data to demonstrate what you want to accomplish because words aren't working.

 

[GinaWhipp]

Hmm, yep, after reading your replies and that of everyone else I'm missing the point. Zero will allow you test for the Date and does nothing to affect the Sum. Perhaps sample data will help.