Calling dates from two fields... IIf - IsNull help.

[seantheghost]

Hello,

I need to display events from a table based on the event date. Each attendee
has a different "Compliance date" that corresponds with which event dates
pertain to their needs. For example, if Person A's compliance date is
12/31/2010, then I need to display all dates 3 years prior to 12/31/2010. I
got that expression to work as follows:
[EventDate]>DateAdd("yyyy",-3,[ComplianceDate])

Now, it gets tricky. There is also a field in the table called [IndDate]
that represents if a person watched a recording of a past event - which means
even if the EventDate is prior to their compliance period, they can still get
credit if they watched the recording within the compliance period. For
example, Person A missed the original event on 1/1/06 (which would be before
their compliance period and no longer important). However, Person A watched
the recording of the event on 1/1/07, which does fall in the compliance
period.

I tried this expression:
=IIf(IsNull([IndDate]),([EventDate])>DateAdd("yyyy",-3,[ComplianceDate]),([IndDate])>DateAdd("yyyy",-3,[ComplianceDate]))

To me, this should say, if there is no value in the IndDate field (most are
blank), then give me the EventDates that are within 3 years of the
ComplianceDate. But, if there is a value for IndDate, then give me all the
IndDates within 3 years of the ComplianceDate. This isn't working though, and
I'm not sure what I'm doing wrong. I'm not getting any results at all when I
run the query.

Thanks for any assistance!

 

[Ken Snell [MVP]]

You cannot put criterion strings in the IIf statement as the result for the
True or False condition.

You need to use a more complex WHERE clause:

WHERE ([IndDate] Is Null AND
[EventDate]>DateAdd("yyyy",-3,[ComplianceDate])) OR ([IndDate] Is Not Null
AND [IndDate]>DateAdd("yyyy",-3,[ComplianceDate]))