Can you use the Count() func. with criteria??? 2

[Tmconsult]

In a report I have a field named [Present] which is a check box. I need to have a text box on the footer which will count the number of [Present] but only if it is checked off as yes. I know the count function, Count([Present]), but can you put criteria in there?

 

[Aivars]

=dcount("Present","MyTable","Present=true&quot

Aivars

 

[Tmconsult]

I keep getting errors. it says I have the wrong number of arguements. Im putting this function in the control source of the unbound textbox

 

[Aivars]

Put this string into Data source property text box on Properties window of text box on the footer

MyTable ->>> your table name where is Yes/No field Present

Aivars

 

[Tmconsult]

for the textbox on the footer, in the properties window there is only: name and control source, no datasource. I am using Access97, is that why? Also this report is based on a query. but Im still getting an error. I have in the control source this: =dcount("Present","MyTable","Present=true&quotand its not working. this report groups by name and then lists events for that person and weather or not they attended, which is denoted by the check box having a check in it. so there are many check boxes listed on the report. then in the group footer for each person it tells me how many total events there where (I figured that out), and then how many they attended (what I need).

 

[mac318]

Is the name of your table "MyTable" or did you copy Alivars' code literally? It should work.

Do you have the checkbox on the report. Unlike forms, you must have each control which you address on the report. You can hide the checkbox; but, it must be on the report.

mac

 

[Aivars]

Sorry!
In such case you can use function Sum()
=abs(sum([Present]))
because constant True=-1, False=0

Put this string into Control source property text box on Properties window of text box on the group or/and report footer.

Aivars

 

[Tmconsult]

Thanx for your patience aivars, it did work. I know what the sum function is but what is abs, and why does this work and not the count. thank you though for your help.

 

[Aivars]

Function Abs() return absolute number of numeric expression.
abs(-3)=3
abs(3)=3

 

[tempclerk]

=dcount("[Present]","MyTable","[Present]=true&quot should have worked also - did you try it with the brackets?

 

[mac318]

Great logic, Aivars. I had to give you a star on that one.

mac

 

[Tmconsult]

tempclerk I actually did get the function to finally work but by having the table name it counts all of the [present] = true for the whole table. my report needs the count by individual person. aivars solution worked very well and it was simple