Access 2007 Filter a Calculated IF Statement Field

0
votes

I have a query that has a this calculated field:

MonthEligible: IIf([5yrAnniv]>=DateSerial(Year(Date()),Month(Date())+1,1),[5yrAnniv],IIf([5yrAnniv]<=DateSerial(Year(Date()),Month(Date())+1,1),DateSerial(Year(Date()),Month(Date())+1,1),""))

And then I have another field that's also calculate based on this:

Eligible?: IIf(DatePart("m",[MonthEligible])=(DatePart("m",Date())+1),"YES","NO")

Basically, the first one calculates the month in which they are eligible, and the second one says "YES" if that month is next month, or "NO" if it isn't.

From there, I want to filter the second field to just show the YES records. But when I put "YES" in the criteria and click View, Access prompts me to put in a value for [MonthEligible]. How do I get it to not do this? I want it to use the existing [MonthEligible] field that works when there's no criteria.

1
ms-accessms-access-2007

1 Answers

0
votes

You logic makes little sense. What you probably are after, could be something like this:

MonthEligible: IIf([5yrAnniv]>DateSerial(Year(Date()),Month(Date())+1,1),[5yrAnniv],DateSerial(Year(Date()),Month(Date())+1,1))

Eligible: IIf(DateDiff("m",Date(),[5yrAnniv])>1,"YES","NO")