Hi,
I have a report like this:
Avg Max Min Deviation Sample size
10 25 5 2 33
12 23 6 -1 35
13 18 8 3 40
14 14 7 -2 30

I am trying to write an expression that counts the negative numbers of the Deviation field. There will be a text box added right under the Deviation field. How can I count the negative numbers in the Deviation field? Thanks...

You can use a DCount function on the record source... something like

=DCount("[Deviation]", "Table Name", "[Deviation] < 0")

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division

Do you mean Sum the negative numbers? Or sum the negative numbers with the positive? Or sum the negative numbers AS positive numbers along with the positive, etc?

I am assuming this is on a report.

Put a text box on the group footer, or footer of the report, and then just type in =Sum([Deviation]) to get that sum.

If you want the negatives as positive, and the positive, you can do: =Sum(Abs([Deviation]))

You can also put the text box on the footer or header (group or report) and put code on the On Format event for that section to create your equation and put the result in the text box, etc.

Did any of that help?

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Hi,
Thanks for your help. I actually wanted to sum the negative numbers of the deviation field.

If this were on a report, which I am assuming, then I would do this:

Put a text box on the report header or footer, then put this code in the text box:

What this does is Sum the Deviation values if they are less than 0, else it sums a zero.

So for this data:

Text Deviation
Row1 -1
Row2 -2
Row3 -5
Row4 6
Row5 7

The textbox returns -8.

Is that what you want?

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Hey, it worked! Thank you very much for your help. I really appreciate it.

I have one more question. How do I link numbers in 2 reports? For example, I have 2 reports Deviation and Summary. Deviation report has a st dev field. How can I link a st dev number from this report into the Summary report?

The very simplest way is to open the other report as acHidden, and then take the values from the report control. Make sure the On Close event of your visible report closes the other report. I would use 2 copies of each report: Deviation, Deviation_Hidden, Numbers, Numbers_Hidden, just so there is no overlapping code.

The most elegant way to do this is to create public function in a module that gathers that data regardless of where the call is from. So the control on the Numbers report would be: fnDeviation(), and vice versa.

I always use ADO since I always do Access / SQL, so maybe Greg's code would work best for this.

Did that help?

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com