DCount for Yes/No Fields 1

[bethabernathy]

Hi There:

I am trying to count the number of "Collisions" where the data is "yes" in a report that is based on a query, but I want the total number of "Collisions" in the whole database not those pulled in the query record set. So this is where I am at:

=DCount([Collision]=1,"tblincident&quot

This is counting all the records for "Collision". What I want to count all the "Collision" in the database where the value is "Yes". I am working on trying to use the

=DCount(Abs([Collision])),"tblincident&quot

as this "Yes/No" field is giving me some problems, but the above syntax is wrong. Any ideas??

Thanks for everything.

Beth

 

[jebry]

Hi Beth!

Use this syntax: DCount("[Collision]", "tblIncidents",
"[Collision] = 1&quot

The general syntax for all of the domain aggregate functions is Function(Field, Table or query, Criteria) and I think each needs to be a string.

hth
Jeff Bridgham

 

[JoeMiller]

Actually.. it would be better if you used DSUM to do this. Yes/No field types have values of -1 and 0 respectively. So if you add the values together you'll have the answer for the number of yes's. Then you need to take the absolute value of it to get a positive answer. So here's how the code would go using the field and tablename above:

=Abs(DSum([Collision]),"tblIncident&quot)

HTH Joe Miller
joe.miller@flotech.net

 

[bethabernathy]

Hi Joe- I tried using this:

=Abs(DSum([Collision]),"tblIncident&quot)

and I get the message "the expression you entered has a function containing the wrong number of arguments"

I have tried to fool with this and can't seem to get it.

Thanks for your help.

Beth

 

[jebry]

Hi Beth!

Joe's formula should look like this:

=Abs(DSum([Collision],"tblIncident&quot)

He had one too many close parentheses.

hth
Jeff Bridgham

 

[bethabernathy]

Hi There Jeff: I put your expression into my unbound text box and got a result of "0".

I wonder if there is a way to do it with the Abs going after the DSum?

Then I tried this one:

DCount("[Collision]", "tblIncidents", "[Collision] = 1&quot

and get the message: "Syntax error - missing operator in expression.

This gets pretty complicated, huh??

Thanks for everything. Beth

 

[jebry]

Hi Beth!

A couple of things I missed:

=DCount("[Collision]", "tblIncidents", "[Collision] = -1&quot

Put this in your unbound text box and see if it works for you. And you are right, a problem like this shouldn't get so complex!

hth
Jeff Brdigham

 

[bethabernathy]

Hi There Jeff:

I am still getting an error. Any ideas about using the Abs function like this:

=DSum(Abs("[Collision]=-1&quot,"tblincident&quot

Of course, this isn't working, but I have had luck with using Abs after the DSum for example in my crazy report this is working great:

=Sum(Abs([Collision]))

but this just sums the values in the query. I am wondering how to use this format with the DSum or DCount?

Also, with this one I get the value of -1:

=DSum("[Collision]","tblincident","[Collision] =-1&quot

Any other ideas?? Thanks Alot - I really appreciate it.

Beth

 

[jebry]

Hi Again!

One more time! Try:

=Abs(DSum("Collision", "tblIncidents&quot) or

=DCount("Collision", "tblIncidents", "Collision = -1&quot

I've tried both of these and they are working well. You might want to check the spelling of the field and the table to make sure they are valid.

hth
Jeff Bridgham

 

[CosmoKramer]

Beth,

Jeff's earlier post should work:

=DCount("[Collision]", "tblIncidents", "[Collision] = -1")

Is the table name tblIncident???

Or, how about a variation of Joe's:

=Abs(DSum("[Collision]","tblIncident"))

 

[bethabernathy]

Hi Jeff: They both worked perfectly!!!! Thank You!!

Beth