4 Replies Latest reply: Nov 17, 2015 2:49 PM by Andii Toole RSS

    Using a Null Field in an Expression

      HI everyone, I'm hoping you can help, I have data that look likes below:





      What I am trying to do, is looking at a specific month, I want to see all orders ordered before that month, that could have shipped the current month but didn't.


      For Example:

      When looking at February - I have one order that was ordered before that month, but didn't ship the month it was ordered (which is a separate table in my App) and it wasn't shipped in February. Because it is a null field, if I select a ShipMonth of February it would go away, and if I select an OrderMonth of February, it would go away. However I DON'T want the order in July to appear in this selection, because it didn't have the potential to ship in February.


      So if I selected August. both the order in February and the Order in July should show up.


      How do I get this to work?


      Right now I have:

      =IF(IsNull(ShipDate) and (Month(OrderDate)<GetCurrentSelections(ShipMonth)), OrderDate)


      If I eliminate the third part of the IF Statement, and keep it to just:

      =IF(IsNull(ShipDate), OrderDate)


      I get every order that hasn't shipped, but I really need to make the Month selection and look at everything that was ordered prior to the month I select.