Selecting a NULL as a '0' 2

[cdck]

I have a complicated query which pulls sums created by 9 other queries into one row of data based on a job number. Each of the 9 sub-queries groups by the job and part number, then sums the amounts in question. If the job number does not exist, that sum will show as a null in the main query.

I need to know how to make that sum show as a 0 if it is NULL.

Here's a simpler version of the main query - how can I modify this to replace Nulls with 0?

[tt]
SELECT [Jobs Query].fpartno, Sum(qryMatTotTot.MaterialTot) AS MaterialTot
FROM ([Jobs Query] LEFT JOIN qryMatTotTot ON ([Jobs Query].fpartno=qryMatTotTot.fpartno) AND ([Jobs Query].fjob_so=qryMatTotTot.fjobno))
GROUP BY [Jobs Query].fpartno;
[/tt]

Cheryl dc Kern

 

[dhookom]

Try:

SELECT [Jobs Query].fpartno, Nz(Sum(qryMatTotTot.MaterialTot),0) AS MaterialTot
FROM ([Jobs Query] LEFT JOIN qryMatTotTot ON ([Jobs Query].fpartno=qryMatTotTot.fpartno) AND ([Jobs Query].fjob_so=qryMatTotTot.fjobno)) 
GROUP BY [Jobs Query].fpartno;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cdck]

Works great, thanks. Is the Nz function supported in versions of Access prior to 2003?

Cheryl dc Kern

 

[JoeAtWork]

Is the Nz function supported in versions of Access prior to 2003?

At least as far back as Access97, as far as I know. In any case, it's pretty easy to make your own.

Public Function nz(ByRef vNullCheck As Variant, Optional vResult As Variant) As Variant
        
    If IsNull(vNullCheck) Then
        If Not IsMissing(vResult) Then
            nz = vResult
        Else
            nz = 0
        End If
    Else
        nz = vNullCheck
    End If

End Function