DCOUNT Function in MS Acess 97 (reports)

[snoopy92211]

Greetings.

I am trying to figure out this DCOUNT Issue. I'm trying to (in a report) count the number of items in the item table where the completion field is NOT withdrawn

Here's my control source. It's not working. I'm thinking that I can't use the dcount function with a not equals to, but is there any other way i can do this?

=DCount("ItemID","IRSItem","COMPLETIONPERCENTAGE NOT 'W/D'")

HELP!!

thanks!

 

[dhookom]

If you want to count against all records in the table then your expression might be:
=DCount("ItemID","IRSItem","COMPLETIONPERCENTAGE <> 'W/D'")
If you only want to count this condition based on records in your report, you may be much better off using:
=Sum(Abs([COMPLETIONPERCENTAGE] <> "W/D"))


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[snoopy92211]

Thank You. Here's another question.

For DCount, can you add additional criteria.

For example:

=DCount("ItemID","IRSItem","COMPLETIONPERCENTAGE <> 'W/D' and "DateSubmitted = 'NULL' ")

The date submitted field is actually a yes/no field. If left unchecked, thats null, am i correct in this assumption?

Thanks again!

 

[dhookom]

I would try to avoid using DCount() for performance reasons... however:
=DCount("ItemID","IRSItem","COMPLETIONPERCENTAGE <> 'W/D' and DateSubmitted = 0")
An unchecked yes/no field has a value of 0. Checked equals -1.

You had an extra double-quote in your expression. Also, the word "Null" does not equal the value of Null. Actually Null=Null is not true.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[snoopy92211]

oops. Actually the Date Submitted field is a date field. If it's empty....that's what i need. That's why I said Null..

sorry about that.


=DCount("ItemID","IRSItem","COMPLETIONPERCENTAGE <> 'W/D' and DateSubmitted = NULL")

an empty field doesn't mean null?

thanks

Duane you've helped me immensely

 

[dhookom]

You can't compare any value to Null. You should use IsNull().
=DCount("ItemID","IRSItem","COMPLETIONPERCENTAGE <> 'W/D' and IsNull(DateSubmitted)")
Keep in mind this will count for all records in the table regardless of the records returned by the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[snoopy92211]

so if i wanted to say datesubmitted is NOT null...how would i do that?

and what do you mean by this:


Keep in mind this will count for all records in the table regardless of the records returned by the report.

 

[dhookom]

=DCount("ItemID","IRSItem","COMPLETIONPERCENTAGE <> 'W/D' and Not IsNull(DateSubmitted)")

You may have a report based on the table/query "IRSItem". This report may be opened with a filter that limits the records to only half of the total records in IRSItem. The number returned from DCount() may then be very different from the count of records in the report.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[snoopy92211]

So what would function you suggest that I use instead of using DCOUNT, since DCOUNT may not be accurate.

 

[dhookom]

I did provide a suggestion in my first reply.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[snoopy92211]

I keep getting an error message when I use that function.

 

[dhookom]

The expression must be entered into a report or group footer. It assumes you have the field COMPLETIONPERCENTAGE in your report's record source.
To also limit to non-null values in DateSubmitted, use:
=Sum(Abs(COMPLETIONPERCENTAGE <> 'W/D' and Not IsNull(DateSubmitted)))


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[snoopy92211]

Duane,

I am going nuts!! This is giving me an Error. I don't understand why this code isn't working.

When I put this in the Control Source field within this report, (and switched to print view), i get an error message. (#ERROR)

=DCount("ItemID","IRSItem","COMPLETIONPERCENTAGE <> 'W/D' and Not IsNull(DateSubmitted)")

 

[dhookom]

Do you have a table/query named IRSItem? Does it have fields ItemID, CompletionPercentage, and DateSubmitted? If so, press Control+G to open the debug window. Enter:[blue][tt]
? DCount("ItemID","IRSItem","COMPLETIONPERCENTAGE <> 'W/D' and Not IsNull(DateSubmitted)")[/tt][/blue]
and press enter. Do you get an error?

What is the name of the control?


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[snoopy92211]

Hmm...I don't get an error when I use the Debugging Window.

Ok strange...now it works! sheesh.

Thank you so much!