Formula looking for dates with no value

[steverdan]

I am looking for a duel qualifier when it comes to dates. I would like to find all of the orders that shipped late based on

{shipped.date}

{due.date}

SO I got the {shipped.date} > {Due.date} and that returns orders that have shipped late late, but not the ones late that haven't shipped. What do I need to find the null values or blank values in the database.

[DellSC · Accepted solution]

1. You have to check for null BEFORE you check for a value in a field. When you compare anything to null, the result is null - not true or false - and processing stops there.

2. Your parentheses aren't grouping the statement correctly and the formula can be simplified like this:

{Delivery.Promised_Date} in DateTime (2021, 1, 1, 00, 00, 00) to DateTime (2021, 12, 31, 00, 00, 00)) And
(
  IsNull({Delivery.Shipped_Date}) or
  (
    {Delivery.Shipped_Date} in DateTime (2021, 1, 1, 00, 00, 00) to DateTime (2021, 12, 31, 00, 00, 00) and
    {Delivery.Shipped_Date} > {Delivery.Promised_Date}
  ) 
)

NOTE: I used indents to make this easier to read...

-Dell

[DellSC]

Try something like this:

((IsNull({shipped.date}) and {due.date} <= CurrentDate) or {shipped.date > {due.date})

Notice where I put the parentheses - because of the "or", these are required in order to get this to work correctly - especially if you have any other selection criteria in the Select Expert.

-Dell

[steverdan]

ok so I used your formula as a base and it works fine on its own for current orders, but I needed previous orders which will eventually be set by parameters (forgot to mention that). So I added those date qualifiers and it now leaves off at least one order. It is an order that was due 12/28/21 but has not shipped yet. What have I got wrong? I split between those that shipped last year (to include orders shipping late from the previous year), and those actually due last year. It created duplicate entries until I grouped them by order number and then moved the details to the grouping. Here is my modified formula.

({Delivery.Shipped_Date} in DateTime (2021, 1, 1, 00, 00, 00) to DateTime (2021, 12, 31, 00, 00, 00) and

{Delivery.Shipped_Date} > {Delivery.Promised_Date}) or

({Delivery.Promised_Date} in DateTime (2021, 1, 1, 00, 00, 00) to DateTime (2021, 12, 31, 00, 00, 00) and

((IsNull({Delivery.Shipped_Date}) and

{Delivery.Promised_Date} in DateTime (2021, 1, 1, 00, 00, 00) to DateTime (2021, 12, 31, 00, 00, 00)) or

{Delivery.Shipped_Date} > {Delivery.Promised_Date}))

[steverdan]

see my notes below.

[steverdan]

Seems to be working. Thank you.