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


G

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"
 
Ad

Advertisements

J

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top