Best practice replacing null date in a report with alternate

[Andre77k]

I have a query that returns all the orders the were closed during a given week, and all the orders that are still open.

Then I have a report based on that query, that lists all the orders by the date. The report looks perfect except for the open orders, which of course do not have a date closed.

So open orders show up as #type! for their date.
For those orders, I would like to replace the closing date, which doesn't exist, with the date the order was opened.

I'm not sure how to deal with this. Do I need a if/then function in my query to replace null closing date, with open date?
Or do I need an expression in the report itself to replace null closing, with the open date?

Or do I need to have a sub-report that uses an open order query which already exists, inside my existing report?

What do more experienced people recommend?

[twinnyfo]

Andre77k,

Welcome to Bytes!

Something sounds wrong with your Query itself. If you are including a Date Field, and it happens to be Null, then it will show up as Blank on your Report. Are you trying to manipulate the Date somehow?

It would be helpful to include your query here and we can take a look at it.

[PhilOfWalton]

In your query you have several options.
Add a new field "NewClosingDate"

NewClosingDate: Iif Not  IsNull(ClosingDat, ClosingDate, OpenDate)
 

Or to my mind, better still as it will give a blank NewClosingDate

NewClosingDate: Iif Not  IsNull(ClosingDat, ClosingDate, "")
 

Your report obviously uses the NewClosingDate

Phil

[twinnyfo]

Phil,

For proper Syntax, IsNull() needs to evaluate just the Date, without additional arguments (and i remived the "Not" for clarity's sake:

NewClosingDate: IIf(IsNull(ClosingDate), OpenDate, ClosingDate)

Or:

NewClosingDate: IIf(IsNull(ClosingDate), "", ClosingDate) 

However, Null Dates should not display with #Type; they should simply be blank. If we fix that problem first, then we can choose to display the Open date instead (but I think that would be confusing for anyone reading the report).

[Andre77k]

That was the answer I was looking for.

IIf(IsNull([DateOut]),[DateIn],[DateOut]) AS FinDate

This ended up being my exact solution, based on your both of your posts. Thank you.

[twinnyfo]

I'm glad we could find a solution you can work with.

Did we ever figure out why the dates were displaying so strangely in the Report?