Count Function for an Expression 1

[jdd3110]

Hi:

I am making a report where one of my fields comes from an "if" expression in my query. It simply prints out "yes" or "no" in the report detail for each entry. What I would like to do if have a count total of yes's and no's in my report footer, but I am not sure how to do so. Any help would be appreciated.

Thanks,
Josh

 

[batteam]

OK, here's what you gotta' do.

Put a text box in your report next to the Yes/No textbox. Lets call that new textbox textMarker and let's say your 'Yes' or 'No' text field is called txtYesNo. Make txtMarker invisible, but then behind the report in the detail section, put something like the following:

Select Case txtYesNo
Case Yes
textMarker = 1
Case No
textMarker = 0
End Select

Then in your report footer, you can simply sum the field textMaker. If you have 10 records, but the sum is 6, this means you have 6 'Yes' answers and then you can do a formula in another textbox to subtract 6 (or Sum of textMarker) from the total COUNT of textMarker (or txtYesNo) and get 4, for 4 'No' responses.

Try this - hope you get the general idea. Good luck.

 

[dhookom]

A better method would use a control source in the report footer text box of:
=Sum(Abs([YourQueryColumn] = "Yes"))
and
=Sum(Abs([YourQueryColumn] = "No"))

Using a code solution rather than an expression to accumulate values like this is prone to errors.


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

 

[jdd3110]

Thank you both very much...both ways worked perfect!!!