Preventing null value in total query

[Swift_74d]

Null might not be the right term but for all intents and purposes you'll get the idea. I have a query that is supposed to sum the prior quarter(s) monthly adjustments however it is possible that no such adjustments exist. This number is then passed on to another query that totals this with a similar result from another query (for prior payments). The problem I’m having is that if there were no prior adjustments then the query passes what I believe is a null value and when added to an existing value from the second query produces yet another null value. I've tried several IIF Is Null variations to produce a 0 if no total is produced but nothing seems to work for me.

The query's sql is set up the following way...

[FONT=Calibri][SIZE=3]SELECT Sum(Adjustments.Adjustment) AS PriQtrAdj[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]FROM UR_Form LEFT JOIN Adjustments ON UR_Form.Ye = Adjustments.Adjustmentyear[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]WHERE (((Adjustments.Adjustmentyear)=[Ye]) AND ((Adjustments.Adjustmentmonth)<IIF([QTR]=2,4,IIF([QTR]=3,7,IIF([QTR]=4,10)))) ((Adjustments.Company)="[Co]))</font" AND>[/FONT]
[SIZE=3][FONT=Calibri]GROUP BY UR_Form.ID[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]HAVING (((UR_Form.ID)=1));[/FONT][/SIZE]

Any idea on how to produce a value of 0 if no total is produced?
[/SIZE]

 

[boblarson]

Use the NZ function:

Select Sum(Nz(Adjustments.Adjustment,0)) As PriQtrAdj

 

[Swift_74d]

SELECT Sum(Nz(Adjustments.Adjustment,0)) AS PriQtrAdj
FROM UR_Form LEFT JOIN Adjustments ON UR_Form.Ye = Adjustments.Adjustmentyear
WHERE (((Adjustments.Adjustmentyear)=[Ye]) AND ((Adjustments.Adjustmentmonth)<IIf([Qtr]=2,4,IIf([Qtr]=3,7,IIf([Qtr]=4,10)))) AND ((Adjustments.Company)=[Co]))
GROUP BY UR_Form.ID
HAVING (((UR_Form.ID)=1));

Thanks for the quick reply, however the above change is still producing a "blank" value.

 

[james_lankford]

SELECT 
  nz ( Sum(Adjustments.Adjustment), 0 ) AS PriQtrAdj 
FROM 
  UR_Form 
    LEFT JOIN 
      Adjustments 
        ON 
          UR_Form.Ye = Adjustments.Adjustmentyear
WHERE 
(
  (
    Adjustments.Adjustmentyear = [Ye] 
  ) 
  and 
  (
    UR_Form.ID = 1 
 )
)

 

[Swift_74d]

Thanks James, but still blank...and as an added update, if i go into the adjustments table and enter a $0 adjustment everything totals fine. Ultimatly i'd like to avoid having to enter and adjustment if it doesn't exist but if there's no solution then i guess it can be lived with.

*Update*
After seeing James' Edit i modified it to the following, which now works.

SELECT nz(Sum(Adjustments.Adjustment),0) AS PriQtrAdj
FROM UR_Form LEFT JOIN Adjustments ON UR_Form.Ye = Adjustments.Adjustmentyear
WHERE (((Adjustments.Adjustmentyear)=[Ye]) AND ((UR_Form.ID)=1) AND ((Adjustments.Adjustmentmonth)

<IIF([QTR]=2,4,IIF([QTR]=3,7,IIF([QTR]=4,10,0)))) p ((Adjustments.Company)="[co]));" AND Code]< [>

 

[james_lankford]

glad it works, but I am not understanding this at all

AND ((Adjustments.Adjustmentmonth)</pre>

 

[Swift_74d]

for some reason it keeps cutting off after a less than sign....

 

[james_lankford]

I saw that

"here's the full code"
and it was the same thing as before
ha ha ha