Iif isnull doesn't return 0 values 1

[jmshik]

I have tried numerous variations of this query, to no avail.
Weather table contains many fields including dates and rain amount per date. I need a result that will list the calendar week, and the count of days that had rain>0, including ALL the week numbers ie: Calendar Wk#18 had no days with rain and I want that to show. Thank you for any help!!!

SELECT DatePart("ww",[date],2,2) AS KW, count(IIf(IsNull([rain]),0,[rain])) AS CtRain
FROM apg
WHERE (((Year([date])) Between 1993 And 2002))
GROUP BY DatePart("ww",[date],2,2)
HAVING sum(rain)>0;

 

[tlbroadbent]

You should use a SUM aggregate function and sum 0 or 1 in the query rather than count 0 or [Rain]. If you want to show weeks with zero rain days then remove the Having clause.

SELECT
DatePart("ww",[date],2,2) AS KW,
Sum(IIf(IsNull([Rain]) Or [Rain]=0,0,1)) AS CtRain
FROM apg
WHERE (((Year([date])) Between 1993 And 2002))
GROUP BY DatePart("ww",[date],2,2);

Alternately, if your version of Access has the Nz function you can rewrite as follows. The NZ function will substitue 0 for null and simplify the query.

SELECT
DatePart("ww",[date],2,2) AS KW,
Sum(IIf(NZ([Rain],0)=0,0,1)) AS CtRain
FROM apg
WHERE (((Year([date])) Between 1993 And 2002))
GROUP BY DatePart("ww",[date],2,2); Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jmshik]

Tlbroadbent, THANK YOU!!!
You have saved me so much time!
jmshik

 

[jmshik]

Hi tlbroadbent AGAIN!
This query worked perfect in Access, thank you! But when I attempt to import the result w/MsQuery from Excel, it tells me I can't access the table! I have used msquery, extensively, and never had this problem. I tried creating new queries, in new .mdb's, still the same result ONLY with this query!
Please help!