Count Function Help

[JPW]

I have a report (linked to a query) and in the footer I already have one count calculation that works. Basically it counts all the rows in the report and gives me the correct number.

However I have a column in my report with the field 'Collected By'. That's fine, and in the next column next to it I have that as 'Disposed'. In a that coloumn I have a text box that generates the word 'Yes' if the word 'ASAL' is in the 'Collected By' field/column.

Now I would like a count function or something to count all the 'Yes' in the Disposed field/column.

 

[boblarson]

this should do it for you:

put a text box in your report footer and have:

=Sum(IIF([YourYesField]="Yes",1,0))

 

[JPW]

No not working, when the report is opened a box appeas asking to enter paramater value.

 

[boblarson]

What did you substitute for YourYesField?

 

[JPW]

I entered the text 'fd' which is the name of the field that contains the word "Yes"...If there is no "Yes" in that field then that field is just blank.

=Sum(IIf([fd]="Yes",1,0))

 

[boblarson]

If it is blank then it would be null in this case and so nulls will kill you every time. Try this instead:

=Sum(IIf(Nz([fd],"")="Yes",1,0))

 

[JPW]

No.

I still get the 'enter paramamter value' box everytime i open the report, and when in the report the box that contains the code is '0'

I'll try and explain my report more just to make sure.

Report linked to query.

I have around 8 columns/fields.

I have a column/field called 'Collected By'. And this will display the data such as 'ASAL', 'GF', 'RAC' and others. This is obviously stored in the database.

I have another field/column of which doesn't store data in the database and that's the Disposed field/column of which I've named 'fd'

In that field I have the code '=IIf([collectedby]="ASAL","Yes")' This displays the word "Yes" if criteria is met and leaves field blank if it's not met.

....


To generate the report I have a form which has two calender controls linked to query to generate a date from and date to criteria and then press a command button to bring up the report.


Report works perfectly well, it's just this counting the number of times the word "YES" is causing problems.

 

[boblarson]

try changing your count box to this:

=Sum(IIf(Nz([collectedby])="ASAL",1,0))

 

[JPW]

No still no luck

 

[JPW]

Sorry, it's working.

Perfect - thank you very much.

 

[boblarson]

If it is working to put the "Yes" in it should also work to do the sum. Can you post the db?

 

[boblarson]

If it is working to put the "Yes" in it should also work to do the sum. Can you post the db?

Good, since you got it working then ignore my last post.

 

[boblarson]

Sorry, it's working.

Perfect - thank you very much.