Need to be able to select a blank date field, but how? 1

[WaukMyWay]

CR10
I have a date field in a table which is not always filled in, therefore, it is blank.
There is also a date range parameter which I must test for.
I know how to test for the entered date range, but am not sure what to do about the blank date field.
I have tried using IsNull, but it did not select records with the blank date field.

 

[lbass]

Try:

(
{table.date} = date(0,0,0) or
{table.date} = {?daterange}
)

-LB

 

[WaukMyWay]

I just tried it and received an error message - "Failed to open a rowset"..."Invalid date escape.".
Here is my code (less the actual table name):
(
{Table.Date} in {?FromDate} to {?ToDate} or
{Table.Date} = date(0,0,0)
)
What is wrong?

 

[kskid]

Try

(
{Table.Date} in {?FromDate} to {?ToDate} or
{table.date} = date(0,0,0) or
isnull({Table.Date})
)

 

[lbass]

Null checks must always be first, and I would have done it in the order I showed:

(
{table.date} = date(0,0,0) or
{table.date} = {?fromdate} to {?todate}
)

However, I'm not sure that's the cause. How did you enter the parameter dates?

-LB

 

[WaukMyWay]

Thank you, lbass!
I really did not realize the importance of order with null tests. It works!

Thanks again!

 

[Nimisis]

I am seeking a formula for {allhistory.FromTime}. I need to count how many days ({allhistory.DriverName}) did NOT work.

Running CR 10. on SQL server.

 

[lbass]

Please start a new thread, and also provide more information, e.g., how do you know when a driver DID work? Are you considering then entire week or only business days? Etc.

-LB