Nested IIF Statement not working

[klbrownie]

I have a database where I have two date fields, Procedure Due Date and Procedure Performed Date. I want to update a checkbox called Procedure over 30 days based on two considerations:

1 - Whenever the Perfomed Date is greated than the Due Date.

2 - Whenever the Due Date has gone by without a Performed date.

I have IIF statements where one situation works, but then wipes out everything that may have been checked by the other situation. I know that I cannot have the two IIF statemnets seperately, but I am having a hard time combining them.

Here are the two statements that I have:

IIf([Procedure Performed Date]>[Procedure Due Date],True,False)

IIf([Procedure Performed Date] Is Null And Date()>[Procedure Due Date],True,False)


I have stared at this so long, my brain hurts! Does anyone have any bright ideas?

[gershwyn]

If I'm understanding your requirement correctly (here's hoping!) then this should work:

IIf(([Procedure Performed Date] Is Null And Date()>[Procedure Due Date]) Or (Nz([Procedure Performed Date],0)>[Procedure Due Date]),True,False)

The Nz function will convert a null value to a zero, which allows us to safely compare it to the due date. That portion of the test will always be false if no Performed Date is given. If either of the two conditions are met, the checkbox will be set to true.

(As an aside, the IIF function isn't strictly necessary. The test conditions will either evaluate to true or false, so you could set the textbox to that value directly. Makes no real difference though, other than slightly less typing.)

Hope that helped.
Gersh

[klbrownie]

Thanks Gersh! I will try it tomorrow and see if that does the job.