Adding Default value of zero when there is no any value

[VSolano]

Hi all;

I am having a hard time adding a zero to a calculated value to a group of fields on a table. I can not add this property under the table because I am deleting and creating the table often.

I tried NZ and Format and I was not able to see default zero when there is not value. Any help will be greatly appreciated.

I want to be able to add zero to "Suminvoiceamt" when there is not value.

rs3.FindFirst "[PlanCarrier] & [EntityID] & [companyid] & [benefitsid] & [PlanType] & [plandatailid]='" & STSearch & "'"
              
                     If rs3!benefitsid <> 15 Then
                     
                          StrCriteria = "plancarrier=" & rs3!PlanCarrier & " And entityid=" & rs3!EntityID & " And benefitsID=" & rs3!benefitsid & " AND plantype=" & rs3!PlanType & " AND plandatailid=" & rs3!plandatailid & " AND year(invoicedate)=" & Me.LBReportyear & " AND month(invoicedate)=" & Month(rs!Invoicedate)
                     Else
                          StrCriteria = "plancarrier=" & rs3!PlanCarrier & " And entityid=" & rs3!EntityID & " And benefitsID=" & rs3!benefitsid & " AND plantype=" & rs3!PlanType & " AND plandatailid=" & rs3!plandatailid & " AND year(invoicedate)=" & Me.LBReportyear & " AND month(invoicedate)=" & Month(rs!Invoicedate) & " AND CompanyID=" & rs3!companyid
                     End If
                    
'                     Debug.Print StrCriteria
                
                                        
                If rs3.NoMatch = False Then
                
                      Invoicedate = rs!Invoicedate
                    
                    Select Case Month(rs!Invoicedate)
                      
                    
                    
       
                        Case 1

                            
                            SumInvoiceAmt = DSum("invoiceamount", "tbbilling", StrCriteria)
                            rs3.Edit
                            rs3!January = SumInvoiceAmt
                            rs3.Update

 

[June7]

Well, Nz() should work:

Nz(DSum("invoiceamount", "tbbilling", StrCriteria),0)

 

[VSolano]

I tried both of these options and it does not work.

I created this field on the table using the tabledef and I created as double.

 

[June7]

"Does not work" means what - error message, wrong result, nothing happens?

What is SumInvoiceAmt - a field or a variable?

 

[VSolano]

Does not works means it does not print zero when the value is null.

The invoiceamount is a field in the new table.

I am pulling the invoice amount from one table and copy into another

 

[June7]

Why duplicate data between tables?

If you want to provide db for analysis, follow instructions at bottom of my post.