Count Totals etc.

[Ilovexfiles]

Hello
I would like to make a report that lists totals...such as:
Total X records: ###
Total Criteria Y records: ###

I have been unsuccessfully trying this via DCount control source in an unbound text box.

Are there other more efficient ways to achieve this?

 

[pbaldy]

You can use a subreport based on a totals query, or this type of thing in the report footer:

=Sum(IIf(Criteria = "X", 1, 0))

 

[isladogs]

I have been unsuccessfully trying this via DCount control source in an unbound text box.

So what happens when you try this?
What conditions are you applying to your DCount formulas?

 

[Ilovexfiles]

So what happens when you try this?
What conditions are you applying to your DCount formulas?

I have been trying to do the following as the control source of my textbox:
=DCount([ScreeningType],[tblScreening],1)

Screening Type is a field/column in the table "tblScreening". I want to know how many in that column equal 1 (which is the Unique Id for a Drop Down List). I have also tried using the actual name of the drop down, but both come back with errors.

 

[Ilovexfiles]

You can use a subreport based on a totals query, or this type of thing in the report footer:

=Sum(IIf(Criteria = "X", 1, 0))

The report I have will not be listing any records at all. So I don't think I can do a footer correct.

I have been able to get individual results in a query by grouping, but it will not let me do multiple groupings (like a pivot table), so I have to do each individually it seems.

 

[pbaldy]

Incorrect syntax:

http://www.theaccessweb.com/general/gen0018.htm

But DCount() probably wouldn't be the tool I used. It's an extra trip to the data that isn't necessary since the report already contains it (and presumably there are several). And if appropriate, the subreport based on a totals query would be more dynamic.

 

[isladogs]

I have been trying to do the following as the control source of my textbox:
=DCount([ScreeningType],[tblScreening],1)

Screening Type is a field/column in the table "tblScreening". I want to know how many in that column equal 1 (which is the Unique Id for a Drop Down List). I have also tried using the actual name of the drop down, but both come back with errors.

The DCount formula is incorrect. Try this:

=DCount("*","tblScreening","ScreeningType=1")

But also look into the approach suggested by pbaldy which works well
In your case, something like:

=Sum(IIf(ScreeningType=1, 1, 0))

EDIT: as Paul & I are tripping over each other here, I'll drop out. Good luck!

 

[pbaldy]

The report I have will not be listing any records at all. So I don't think I can do a footer correct.

I have been able to get individual results in a query by grouping, but it will not let me do multiple groupings (like a pivot table), so I have to do each individually it seems.

Correct on the footer if the report is unbound. My first choice would be the totals query then, so maybe an example of the data and the expected result would help us sort that out (or rule it out).

 

[Ilovexfiles]

thank everyone I kept it simple by correcting D-count formula. will have to do the rest in excel

thanks