Query with Dates and Null Values

[AFKillbait]

I have a Report that pulls its values from a query. The problem I am having is that I need the query to display "Not Completed" if the value for the other field is empty, display a date (using: DateAdd("m", 12, [FieldName]) ) if there is a value, or show "Overdue" if the date in the original field is more than a year old. I already have the added date displayed, it's the null value and overdue date parts that I can't figure out.

Is this possible to do in a query? If not is it possible to do this on the actual Report?

 

[ajetrumpet]

I have a Report that pulls its values from a query. The problem I am having is that I need the query to display "Not Completed" if the value for the other field is empty, display a date (using: DateAdd("m", 12, [FieldName]) ) if there is a value, or show "Overdue" if the date in the original field is more than a year old. I already have the added date displayed, it's the null value and overdue date parts that I can't figure out.

Is this possible to do in a query? If not is it possible to do this on the actual Report?

alan,

i'll assume you have line by line records in the report. if so, you have a couple options that i know of: len() or =NULL. for example, if the CS of the problem control, maybe type:

=IIF([OtherControlName] = NULL, "Not Complete", 
   IIF(dateadd([OtherControlName], "yyyy", 1) < Date(), "Overdue", 
      "ALL IS WELL!"))

moreover, you can substitute =NULL with len() < 1

 

[AFKillbait]

I input that into the expression builder for the field in the query and it came back with #Error. Am I supposed to be putting this into the code for the Report? If yes, which section?

Still pretty new at this, so learning as I go.

 

[ajetrumpet]

I input that into the expression builder for the field in the query and it came back with #Error. Am I supposed to be putting this into the code for the Report? If yes, which section?

Still pretty new at this, so learning as I go.

it may work better behind the report in the code itself. i actually have not been able to figure out the exact reason why IIF() works sometimes and not others. regardless though, try it behind the OPEN() event of the report. it will surely work there..

 

[AFKillbait]

I managed to work it out, just a few minutes ago in fact, by putting that into the Control Source for the text box. Works perfectly, thanks again.

 

[ajetrumpet]

I managed to work it out, just a few minutes ago in fact, by putting that into the Control Source for the text box. Works perfectly, thanks again.

no problem! if i helped out at all. ha ha. good luck to ya...