How do I use the DCount function so it does not count fields with "Null" values in Access?-CodePudding

I have this basic VBA script for conditional formatting. It checks for duplicate values. But this function also retruns True for the empty cells. How do I adjust this formula so it only counts the values that are Not Null?

Is it possible to use the Dlookup function instead to compare if my newly entered Number in me.txt_Number_1 already exists in field [Number_1]?

Private Sub Form_Current()
    
If Nz(DCount("*", "[qry_DataEntry]", "[Number_1] = '" & Me.txt_Number_1 & "'"), 0) = 1 Then

           Me.txt_Duplicate.Visible = False
       Else
           Me.txt_Duplicate.Visible = True

       End If
End Sub

CodePudding user response：

What if you try an IsNull() into the query? Substitute for a field or use multiple with parenthesis. Something like this:

With Me
    If DCount("*", "qry_DataEntry", "Number_1 = '" & .txt_Number_1 & "' AND NOT IsNull(Number_1)") Then
        .txt_Duplicate.Visible = False
    Else
        .txt_Duplicate.Visible = True           
    End If
End With

CodePudding user response：

to count values that are not null, use;

DCount("[Number_1]", "[qry_DataEntry]", "[Number_1] <> Null")

to check the latest entered number, use;

DLookup("[Number_1]", "[qry_DataEntry]", "[Number_1] = " & Me.txt_Number_1)

is Number_1 numeric? numeric fields dont need to be surrounded with quotes when setting search criteria per this example.