Overflow Error In Query (1 Viewer)

Hello,

I have a query in Access 2003 which is as follows:

Actual %: Avg(Format([Total]/[Possible]*100,"Fixed"))

Basically this is causing an overflow error when the field [Possible] = 0.
Now I understand why it's doing this as obviously it's trying to divide by Zero - however, I need this calculation as it's entirely possible that the [Possible] field would have a value of zero and this is correct.

I thought of doing something like

Actual %: IIf([Possible]=0,0,Avg(Format([Total]/[Possible]*100,"Fixed")))

But this gives me an error saying:

You tried to execute a query that does not include the specified expression ..... as part of an aggregate function

How can I get round this please?

Thanks in advance

So you want to include the Average calculation when [Possible] is 0?
Also, if the calculatiion in the Format function is supposed to be a percentage then the bracketting is incorrect in the above code so you can use this:
If you're getting the error message:

You tried to execute a query that does not include the specified expression ..... as part of an aggregate function

Click to expand...

Then you need to right click in any cell and select Totals. Under this field change Group By to Expression

OR

Remove Avg from the code line and select Avg in the Total row.

Thanks for your response.

I've tried:



But it doesn't work. I still get an overflow error.

Also, it is currently set to Expression rather than Group By anyway.

If it helps, I'm trying to get the following:

1) If the possible field is zero then I want my calculation to be 0
2) If the possible field isn't zero then I want my calculation to equal total / possible * 100

In addition to this, it is a summarised query, so if I have 4 rows with data in, I need the average over the 4 rows.

e.g.
Total - Possible
6 - 2
0 - 0
2 - 2
4 - 0

The average should show (4 / 12 * 100) = 33.33%
I hope this makes sense.

Thanks.

Yep, I understand what you're doing.

Let's see your db.