PC Review


Reply
Thread Tools Rate Thread

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

 
 
seantheghost
Guest
Posts: n/a
 
      1st Jun 2009
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!
 
Reply With Quote
 
 
 
 
Ken Snell [MVP]
Guest
Posts: n/a
 
      1st Jun 2009
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]))


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"seantheghost" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
If IsNull, ElseIf IsNull =?Utf-8?B?Sm9jaGVtIERhdmlkcw==?= Microsoft Access Form Coding 8 11th May 2006 12:53 PM
IIF isNull isn't providing the output DMUM via AccessMonster.com Microsoft Access 3 23rd Jan 2006 07:00 PM
IsNull and IIf together =?Utf-8?B?TU1I?= Microsoft Access 3 21st Jun 2005 05:03 PM
IsNull and IIF and error ;) Krzysztof Bartosiewicz Microsoft Access Forms 1 9th Jun 2005 02:50 AM
IIF, IIF, IIF, IIF Bill Microsoft Access Queries 2 9th Jul 2004 09:40 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:34 AM.