Issue generating a 0 instead of a null value (1 Viewer)

[Eemits]

I am trying to add table of costs against a project and where no costs exist, report a 0 (zero) instead of null to allow a profit/loss figure to be generated.

I have the SQL as:

SELECT tblCostings_Expense.Project, Sum(Nz([tblCostings_Expense],0)).[Inv Value] AS Purchases
FROM tblCostings_Expense LEFT JOIN tblProjects ON tblCostings_Expense.Project = tblProjects.Project
GROUP BY tblCostings_Expense.Project;

which is generating an error: "invalid use of ".", "!" or () . in query expression 'sum(Nz([tblcostings_expense],0)).[Inv value'.


Can anyone please help me remove the error and get this operating correctly.

Thanks in advance,
Malcolm

 

[June7]

You have the Sum() and Nz() wrapping table only, not entire field reference. Move the closing parens.

Sum(Nz([tblCostings_Expense].[Inv Value],0))

 

[theDBguy]

Hi. Welcome to AWF!