Calculation based on conditions

[liquidzoo]

I have a report that is generated via a form, and based on certain conditions, I want 2 text boxes to either have data in them, or be blank, but the data that will be in them must have a specific format if I can do it.

The first box I got easily using an IIf statement to check the form and evaluate a condition, the second is a bit harder.

I need this to do a calculation based on 2 columns, but only include the value in the second if the data in the first is greater than 0. The second column will have data in it regardless of the data in the first, for example:

Column1 Column2
0 15.5
450 10.9
0 8.6
125 18.7

Using this example, I would only want to include the data in column 2 that appears in rows 2 and 4 (it can be put into a variable, that's fine, I just need to make a calculation on it)

Can I do this with a vb Function? If so, how would I accomplish only getting the data I'm looking for from Column 2?

 

[Brianwarnock]

Not clear what the calculation is but in a query
IIf(column1=0, calculation not including Column2,Calculation including column2)

Brian

 

[liquidzoo]

What I need to do is sum up the instances of Column 2 where data exists in Column 1, then divide that by the total of Column 1.

This formula is very specific to the checkbox on the form, so altering the query (that also pulls multiple other reports) would not be the way to do this if I can avoid it. I would like to do it on the Report itself, or in the On Load (?) section for the report in VB.

 

[Brianwarnock]

In an unbound textbox in the report footer
=(sum(iif(column1=0,0,column2))/sum(column1))

The names used will be those in the source query.

Brian

 

[liquidzoo]

The report I'm working with right now has a name footer, and I would like to have it in there if I can, if not I will put it in the Page footer, but I do need the data separated by person, rather than a total for the whole report.