iif statement with And statement in query (ms access 2000)

[Guest]

ok... i'm at my wits end!!

please show me how this will actually work:

YTDFeb: IIf([Enter Month]="Feb" And [complaintmonth]="Jan" or
[complaintmonth] = "Feb"And [complaintyear]=Year(Now()),1,0)

the query accepts the statement, however everything comes up "0"... when
select items should return a "1"

 

[John Vinson]

On Wed, 13 Apr 2005 21:23:09 -0700, "JM Kelly" <JM

ok... i'm at my wits end!!

please show me how this will actually work:

YTDFeb: IIf([Enter Month]="Feb" And [complaintmonth]="Jan" or
[complaintmonth] = "Feb"And [complaintyear]=Year(Now()),1,0)

the query accepts the statement, however everything comes up "0"... when
select items should return a "1"

I hate the thought of how this query will look in December... are you
actually storing text strings in the Complaintmonth field? For a true
year to date, I'd just store the ComplaintDate in a date/time field
(the first of the month if you just want month accuracy), and use a
criterion of

BETWEEN DateSerial(Year(Date()), 1, 1) AND DateSerial(Year(Date()),
[Enter month number:] + 1, 1)

That said - you need to put in some parentheses to handle the OR logic
correctly:

YTDFeb: IIf([Enter Month]="Feb" And ([complaintmonth]="Jan" or
[complaintmonth] = "Feb") And [complaintyear]=Year(Date()), 1, 0)

or (more compactly, especially if you'll be going for twelve months)

YTDFeb: IIf([Enter Month]="Feb" And [complaintmonth] IN ("Jan","Feb")
And [complaintyear]=Year(Date()), 1, 0)


John W. Vinson[MVP]