Setting a default value of 0 in a query

[Access_Novice]

I have a table called "Paid" with a field called "hoursPaid." Then I query this table with a query called "qryPaid." In this query, I apply a SUM aggregate function.



I have a second query called "qryCTPaidHours" which queries the query called "qryPaid."

So here is what I have so far: qryCTPaidHours -->qryPaid --> Paid (table)


In "qryCTPaidHours" in design view, I have a field called "SumOfhoursPaid" which is ultimately derived from the field called "hoursPaid" in the table called "Paid." But in Datasheet View, this same field shows up as "hoursPaid." In this field, I want to add a default value of zero.

I found some code which can supposedly do this but I can't get it to work. Is it because I have to use a query instead of a table after the FROM statement (see below in green)?


Here is the code I am trying:

declare @static_number
set @static_number = 0
 
select @static_number as MyZero, SumOfhoursPaid from qryPaid
 
 
SELECT ClickTime.billingAcct, billingAccounts.Exostar, ClickTime.weekEnding, ClickTime.ClickTimeHours, qryPaid.SumOfhoursPaid, [ClickTimeHours]-[SumofHoursPaid] AS unPaid
FROM billingAccounts INNER JOIN (ClickTime LEFT JOIN qryPaid ON (ClickTime.weekEnding = qryPaid.weekEnding) AND (ClickTime.billingAcct = qryPaid.billingAcct)) ON billingAccounts.billingAccount = ClickTime.billingAcct;

[June7]

Where are you putting this code? That isn't VBA.

I don't see hoursPaid field in that SQL.

If you want 0 to show when field is Null, use Nz() function

Nz(hoursPaid,0)