Search code examples
vbams-accessnullzero

Display zero if null

I have some VBA code on a form which performs a look up of the total number of errors from a query. 

Private Sub cboProjectID_Change() 
   Dim VarTotalErrors As Variant 
   VarTotalErrors = DLookup("[total errors]", "[Project_Total_Errors_Query]", "[Project_ID] = " & VarComboKey) 
   Me.txttotalerrors = VarTotalErrors 
End Sub

What condition would I need to add to this VBA code to automatically populate a value of zero when the total_errors is null?

Solution

  • You can feed the DLookup value to Nz and assign that result to the text box directly, which simplifies the code.

    Private Sub cboProjectID_Change()
    Me.txttotalerrors = Nz(DLookup("[total errors]", _
        "[Project_Total_Errors_Query]", _
        "[Project_ID] = " & VarComboKey), 0)
End Sub