using DCount with IsNull

[jlathem]

Can someone take a look see at what I am doing wrong with using DCount with IsNull?

I got a table that has a field called New_Requests and I need a count of all records where New_Requests is Null. I would have thought this would have been very simple, but apparently it’s not as straight forward as I thought.

Any suggestions?
James


Attempt 1 just returns FALSE:

[FONT=Times New Roman][SIZE=3]New_Requests = IsNull(DCount("Date_Batched", "IPP_Tracking", "Date_Batched"))[/SIZE][/FONT]

Attempt 2 returns 0: (there are 5 records with Null

[FONT=Times New Roman][SIZE=3]New_Requests = DCount("Date_Batched", "IPP_Tracking", "Date_Batched Is Null")[/SIZE][/FONT]

Attempt 3 & 4 just returns the error “Argument not optional”:

[FONT=Times New Roman][SIZE=3]New_Requests = DCount("Date_Batched", "IPP_Tracking", "Date_Batched") & [COLOR=navy][B]IsNull[/B][/COLOR][/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]New_Requests = DCount("Date_Batched", "IPP_Tracking", "Date_Batched" & [COLOR=navy][B]IsNull[/B][/COLOR])[/SIZE][/FONT]

 

[John Big Booty]

You will get a response of 0 from a DCount() but I'm pretty sure you will never a get a null result from DCount()

 

[andy_25]

It may be easier to set a default to the field i.e. set it as 0 then you can count the 0s?

As the previous post said you cannot use IsNull with DCount as DCount will always return a number

 

[jlathem]

You will get a response of 0 from a DCount() but I'm pretty sure you will never a get a null result from DCount()

Thanks John.

Jms

 

[jlathem]

It may be easier to set a default to the field i.e. set it as 0 then you can count the 0s?

As the previous post said you cannot use IsNull with DCount as DCount will always return a number

Thanks Andy

Hey, do you think running a DCount on the Record_ID WHERE another field is null wold work? Something like this?

DCount("[Record_ID]", "MyTable", "Other_Field = " & IsNull)


Jms

 

[jlathem]

It may be easier to set a default to the field i.e. set it as 0 then you can count the 0s?

As the previous post said you cannot use IsNull with DCount as DCount will always return a number

As you probably suspected, DCount("[Record_ID]", "MyTable", "Other_Field = " & IsNull)
DIDn't work!

Thanks for the help anyway.

James

 

[pbaldy]

This should work:

DCount("*", "IPP_Tracking", "IsNull(Date_Batched)")

 

[Galaxiom]

Best to use the * for the field since this does not check for Nulls in the designated field. Since every records has a RecordID value there is no point doing that test.

Try this:

DCount("*", "MyTable", "IsNull(Other_Field)")

 

[jlathem]

Best to use the * for the field since this does not check for Nulls in the designated field. Since every records has a RecordID value there is no point doing that test.

Try this:

DCount("*", "MyTable", "IsNull(Other_Field)")

Thanks for the info. I will give that a try. It's a lot simpler than what I wrote to get around the whole IsNull issues.

This works but it is a bit bulky

Sub Form_Current()
    'This is for the Waiting To Be Batched count
    'Declare Veriables
    Dim ver_MaxRecordCount As Integer
    Dim ver_xCount As Integer
   
    'Count the total number of records
    ver_MaxRecordCount = DCount("[IPP_ID]", "IPP_Tracking")
    'Count the records that have a Date Batched greater than 0
    ver_xCount = DCount("[IPP_ID]", "IPP_Tracking", "Date_Batched > " & 0)
    
    'Get count of Null by subtracting xCount from Max
    New_Requests = ver_MaxRecordCount - ver_xCount
End Sub

 

[jlathem]

Best to use the * for the field since this does not check for Nulls in the designated field. Since every records has a RecordID value there is no point doing that test.

Try this:

DCount("*", "MyTable", "IsNull(Other_Field)")

That works great! And it's a lot cleaner!

Thank you.
Jms