Dcount anomaly (1 Viewer)

[John Sh]

I am using Dcount to count the number of empty (null) rows in various fields in a couple of different tables and in two different forms.
The syntax is identical for both forms, apart from field and table names. In one form Dcount returns the correct number in the other it returns 0 in every case.
All of the fields in question are Null. None have any characters, including " ". Where am I going wrong?
The code is in the "Form_Load()" event in both cases.

This code returns the correct count.
All fields are Text

    fCount = DCount("[CombFamily]", "Fam-Gen-Comp", "[combfamily] = ''")
    gCount = DCount("[Comb-Genus]", "Fam-Gen-Comp", "[Comb-Genus] = ''")
    sCount = DCount("[Comb-Species]", "Species_Comp", "[Comb-Species] = ''")
    nCount = DCount("*", "Name Changes")
Do stuff depending on counts

This code returns 0
Genus and SpeciesEpithet fields are Text. BoxNo field is Integer.

    gCount = DCount("[Genus]", "Main", "[Genus] = ''")
    eCount = DCount("[SpeciesEpithet]", "Main", "[SpeciesEpithet] = ' '")
    bCount = DCount("[BoxNo]", "Main", "[BoxNo] = 0")
    bCount = bCount + DCount("[BoxNo]", "Main", "[BoxNo] = 999")
Do stuff depending on counts

The xCount variables have not been defined in either sub or as publics

Can the last two lines of the second snippet be written in one line as "[BoxNo] = 0 or [BoxNo] = 999"

 

[bastanu]

I think you should use wrap them in Nz:
gCount = DCount("[Genus]", "Main", "Nz ([Genus])=' ' ")
Note there is no space between the single quotes, I added it here so it doesn't look like a double quote.

 

[John Sh]

I think you should use wrap them in Nz:
gCount = DCount("[Genus]", "Main", "Nz ([Genus])=' ' ")
Note there is no space between the single quotes, I added it here so it doesn't look like a double quote.

Thanks.
I had tried "nz([Genus],"") = ''" that didn't work. nor did your method. It still returns 0
Why does the first code snippet work normally and not the second. They are both doing the same simple task.

 

[bastanu]

It depends on the data you have in each table. What is the dfault value for the three fields in the fam-gen-comp table and how do
you clear them in code if you do? If you use something like this to clear the field Me.txtFamily="" then you make the field store a ZLS (zero length string) and your first code would work. If the fields are truly Null it shouldn''t, you would need to use "[combFamily] is Null ")

 

[plog]

If the fields are truly NULL you cannot count them:

DCount("[CombFamily]", "Fam-Gen-Comp", "IsNull([combfamily])")

will always return 0 no matter what.

You must count something that is non-null. That means you leave the criteria argument as it is, but count something else:

DCount("*", "Fam-Gen-Comp", "IsNull([combfamily])")

 

[John Sh]

I was initially using the following code in the second scenario and it worked fine. I just thought using Dcount was a cleaner way of doing things. This code was also in the Form_Load event. I have reinstalled this code and it works fine.

    Dim db As Database
    Dim rs As Recordset
    gCount = 0
    eCount = 0
    bCount = 0
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Main", dbOpenDynaset)
    rs.MoveLast
    rs.MoveFirst
    Do
        If Nz(rs.Fields("genus"), "") = "" Then
            gCount = gCount + 1
        ElseIf rs.Fields("Genus") = "NIL" Then
            gCount = gCount + 1
        ElseIf Nz(rs.Fields("speciesepithet"), "") = "" Then
            eCount = eCount + 1
        ElseIf Nz(rs.Fields("BoxNo"), "") = "" Then
            bCount = bCount + 1
        ElseIf rs.Fields("BoxNo") = 99 Then
            bCount = bCount + 1
        End If
        rs.MoveNext
    Loop While Not rs.EOF
    rs.Close

 

[John Sh]

It depends on the data you have in each table. What is the dfault value for the three fields in the fam-gen-comp table and how do
you clear them in code if you do? If you use something like this to clear the field Me.txtFamily="" then you make the field store a ZLS (zero length string) and your first code would work. If the fields are truly Null it shouldn''t, you would need to use "[combFamily] is Null ")

The default field value is the default. It hasn't been set to anything in the table design. The tables involved were all created from Excel spreadsheets and are handled "as is". Field values may change from time to time but will never revert to empty, albeit null or ZLS.
How does one test for a ZLS string?
Why does Dcount return 0 for the Integer field.
Let's switch the question.

Why does Dcount return the correct information in the first code snippet when the tables were all created the same way and have similar, I.E, mainly string, data.

How dues one upload a section of the tables, and would that help?