Expression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an expression for a query

Reportover: IIf(DateDiff("d",[Due Date],[Rpt Date])>1,1,0)

This works fine except now I have appended some records in which there is a
Due Date for the record but no Rpt Date and it is not adding these to the
total. It works great when there is both a Due Date and Rpt Date.

What do I need to do to account for Rpt Date being blank?
 
1. You can assign a default value incase of a Null, this example assign a
date() incase of a Null

IIf(DateDiff("d",[Due Date],NZ([Rpt Date],Date()))>1,1,0)
========================================
2. Run another iif to check if its Null and then return a value

IIf([Rpt Date] Is Null,0,IIf(DateDiff("d",[Due Date],[Rpt Date])>1,1,0))
 
Chris I get a syntax error.

Here is what I had

Reportover: IIf(DateDiff("d",[Due Date],[Rpt Date])>1,1,0)

You sent
IIf(DateDiff("d",[Due Date],Nz([Rpt Date], <results if true))>1,1,0)

I am confused with <results if true.

I tried

I can't get it to run.

Can you help.








--
Brian


Chris2 said:
bdehning said:
I have an expression for a query

Reportover: IIf(DateDiff("d",[Due Date],[Rpt Date])>1,1,0)

This works fine except now I have appended some records in which there is a
Due Date for the record but no Rpt Date and it is not adding these to the
total. It works great when there is both a Due Date and Rpt Date.

What do I need to do to account for Rpt Date being blank?
Click to expand...

Brian,

There are two choices.

1) You need the Nz function.

Nz(<expression>, <results if true>)

IIf(DateDiff("d",[Due Date],Nz([Rpt Date], <results if true))>1,1,0)


2) Update the blank [Rpt Date] column values with appropriate dates.


Sincerely,

Chris O.
Click to expand...
 
See what Ofer suggested.

Chris was taking a more generic approach by suggesting that you could put
whatever date you wanted where it says <results if true))> (it might have
been easier to understand if it had been written as <results if true>)))

Ofer just assumed you'd want to use today's date if [Rpt Date] is null.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



bdehning said:
Chris I get a syntax error.

Here is what I had

Reportover: IIf(DateDiff("d",[Due Date],[Rpt Date])>1,1,0)

You sent
IIf(DateDiff("d",[Due Date],Nz([Rpt Date], <results if true))>1,1,0)

I am confused with <results if true.

I tried

I can't get it to run.

Can you help.








--
Brian


Chris2 said:
bdehning said:
I have an expression for a query

Reportover: IIf(DateDiff("d",[Due Date],[Rpt Date])>1,1,0)

This works fine except now I have appended some records in which there is a
Due Date for the record but no Rpt Date and it is not adding these to the
total. It works great when there is both a Due Date and Rpt Date.

What do I need to do to account for Rpt Date being blank?
Click to expand...

Brian,

There are two choices.

1) You need the Nz function.

Nz(<expression>, <results if true>)

IIf(DateDiff("d",[Due Date],Nz([Rpt Date], <results if true))>1,1,0)


2) Update the blank [Rpt Date] column values with appropriate dates.


Sincerely,

Chris O.
Click to expand...
Click to expand...
 
I marked as an answer as No. 1 was what I was looking for. I did not need
No. 2 in this case.

Thanks to all.
--
Brian


Ofer said:
1. You can assign a default value incase of a Null, this example assign a
date() incase of a Null

IIf(DateDiff("d",[Due Date],NZ([Rpt Date],Date()))>1,1,0)
========================================
2. Run another iif to check if its Null and then return a value

IIf([Rpt Date] Is Null,0,IIf(DateDiff("d",[Due Date],[Rpt Date])>1,1,0))
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



bdehning said:
I have an expression for a query

Reportover: IIf(DateDiff("d",[Due Date],[Rpt Date])>1,1,0)

This works fine except now I have appended some records in which there is a
Due Date for the record but no Rpt Date and it is not adding these to the
total. It works great when there is both a Due Date and Rpt Date.

What do I need to do to account for Rpt Date being blank?
Click to expand...
Click to expand...
 
bdehning said:
I have an expression for a query

Reportover: IIf(DateDiff("d",[Due Date],[Rpt Date])>1,1,0)

This works fine except now I have appended some records in which there is a
Due Date for the record but no Rpt Date and it is not adding these to the
total. It works great when there is both a Due Date and Rpt Date.

What do I need to do to account for Rpt Date being blank?
Click to expand...

Brian,

There are two choices.

1) You need the Nz function.

Nz(<expression>, <results if true>)

IIf(DateDiff("d",[Due Date],Nz([Rpt Date], <results if true))>1,1,0)


2) Update the blank [Rpt Date] column values with appropriate dates.


Sincerely,

Chris O.
 
bdehning said:
Chris I get a syntax error.

Here is what I had

Reportover: IIf(DateDiff("d",[Due Date],[Rpt Date])>1,1,0)

You sent
IIf(DateDiff("d",[Due Date],Nz([Rpt Date], <results if true))>1,1,0)

I am confused with <results if true.

I tried

I can't get it to run.

Can you help.
Click to expand...

bdehning,

As Mr. Steele pointed out, "<results if true>" is meant to be replaced
by whatever it is you need.

I wrote: "2) Update the blank [Rpt Date] column values with
appropriate dates.", and that was referring to replacing "<results if
true>"

I didn't know what you wanted in place of [Rpt Date], so I wrote in a
phrase meant to indicate you needed to put your own material in that
spot based on your needs. Terms placed between <> are usually meant
to be replaced by something else.


Sincerely,

Chris O.
 
Back
Top