[b]Using DCount to count Nulls in a table?[/b] 2

[cashe]

Hi All,

I'm haveing real problems using DCount in a text box on a form to count nulls in tblAssigned. I need to count the nulls in the End_Date field. A null means that a contract is rolling and therefore no end date. The expression I'm using is:

=DCount("[ContractorID]","[tblAssigned]",IsNull([End_Date]))

What is wrong with my expression? My other DCounts all work.

The Dcount is returning the number of records in tblAssigned. Any help would be much appreciated.

Thank you in advance,

Cashe

 

[hneal98]

Here is a piece from Microsoft Access Help:

The DCount function doesn't count records that contain Null values in the field referenced by expr, unless expr is the asterisk (*) wildcard character. If you use an asterisk, the DCount function calculates the total number of records, including those that contain Null fields. The following example calculates the number of records in an Orders table.

 

[scottian]

have you tried :-
=DCount("[ContractorID]","[tblAssigned]",[End_Date]Is Null)

Be ALERT - Your country needs Lerts

 

[glalsop]

If DCount doesn't work (and by the looks of hneal98's post it doesn't), you could use a record set to count the null's:


Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("select * from YourTableName where YourFieldName is null;&quot

rs.MoveFirst: rs.MoveLast

MsgBox rs.RecordCount

-Gary

 

[cashe]

Thanks Scottian and Gary for the fast response. I've tried both of your methods. I'm still getting the same results as before with Scottian's method. Gary's method works great and qives me the correct value. I will remember not to use DCount for counting Nulls.

Cheers for your help

Cashe

 

[scottian]

The only other i can think of is that the [ContractorID] field, which you are counting, contains nulls, which would throw up the error. if thats the case then you need to count a field which is set as 'required' that way it cant be null and can be counted.

Be ALERT - Your country needs Lerts

 

[cashe]

Hi Scottian,

The [ContractorID] field is a primary key field and therefore contains no nulls. I've looked in to the problem further and you should not use DCount for nulls as an objects control. Glalsop's post works great and gives me the correct total.

Thanks for your help

Cashe

 

[RoyVidar]

Just for the fun of it...

(I use similar approach as demonstrated by glalsop, when needing counts)

The fact that DCount doesn't count records containing Null in the field referenced by expr, can be used in the calculation, although I'd probably never try anything like this

Consider:

[tt]=DCount("*","[tblAssigned]") - DCount("[ContractorID]","[tblAssigned]")[/tt]

- should give the same result

First one get's the total number, second one the number of not null field values, and then calculate the difference...

Roy-Vidar

 

[scottian]

Im not sure if i understand this thread at all, ive just tried :-
=DCount('[field1]','[Table1]','[field2]is null')

and it worked, it gave me the correct count of field1 where field2 contained nulls
you can count nulls, but you cant count the field that contains the null value you have to count a field which doesnt contain a null value

Be ALERT - Your country needs Lerts

 

[cashe]

Hi Scottian,

Thanks for your perseverance. You are absolutely correct. I've used your DCount expression and it works great. These are the DCounts I'm now using:

=DCount('[ContractorID]','[qryContracts]',"[End_Date]is null And [Con_Status]='Needs Contract'")

=DCount('[ContractorID]','[qryContracts]',"[End_Date]is null And [Con_Status]='Pending'")


Once Again, Thanks

Cashe

 

[hneal98]

Well, I guess you can't believe everything you read. What version of Access are you using?

 

[cashe]

Hi hneal98,

I'm using Access 2000.

Cashe

 

[hneal98]

Maybe that is why. I looked it up in Access 97. Oh well.