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

Eemits

New member
Local time
Today, 09:41
Joined
Feb 4, 2022
Messages
1
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

AWF VIP
Local time
Today, 01:41
Joined
Mar 9, 2014
Messages
5,425
You have the Sum() and Nz() wrapping table only, not entire field reference. Move the closing parens.

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

theDBguy

I’m here to help
Staff member
Local time
Today, 02:41
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!
 

Users who are viewing this thread

Top Bottom