By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Best practice replacing null date in a report with alternate

P: 2
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?
Jul 6 '18 #1

✓ answered by twinnyfo

Phil,

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

Expand|Select|Wrap|Line Numbers
  1. NewClosingDate: IIf(IsNull(ClosingDate), OpenDate, ClosingDate)
Or:

Expand|Select|Wrap|Line Numbers
  1. 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).

Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,128
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.
Jul 6 '18 #2

PhilOfWalton
Expert 100+
P: 1,430
In your query you have several options.
Add a new field "NewClosingDate"

Expand|Select|Wrap|Line Numbers
  1. NewClosingDate: Iif Not  IsNull(ClosingDat, ClosingDate, OpenDate)
  2.  
Or to my mind, better still as it will give a blank NewClosingDate
Expand|Select|Wrap|Line Numbers
  1. NewClosingDate: Iif Not  IsNull(ClosingDat, ClosingDate, "")
  2.  
Your report obviously uses the NewClosingDate

Phil
Jul 6 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 3,128
Phil,

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

Expand|Select|Wrap|Line Numbers
  1. NewClosingDate: IIf(IsNull(ClosingDate), OpenDate, ClosingDate)
Or:

Expand|Select|Wrap|Line Numbers
  1. 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).
Jul 6 '18 #4

P: 2
That was the answer I was looking for.

Expand|Select|Wrap|Line Numbers
  1. IIf(IsNull([DateOut]),[DateIn],[DateOut]) AS FinDate
This ended up being my exact solution, based on your both of your posts. Thank you.
Jul 6 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 3,128
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?
Jul 6 '18 #6

Post your reply

Sign in to post your reply or Sign up for a free account.