Report criteria formula help needed

[retro]

Hi guys,

Thanks for all the help in the past... I'm getting on well with my project for a n00b

OK, I have a report, based on a query. The one value in the query that I'm interested in is called Price.

I have a text box called Total that I wanted to display a value, so I used the criteria. It works entering, say, =Sum(Price). But what I want is this:

If the total of all the values from Price is more than 100, then the value of Total is the total of Price minus 100, divided by 2. If not, the value is 0.

Here's an example:

The values under Price are 30, 30, 30 and 40. The total of that is 130. The vaule of Total should read 15. (a simpler formula could be total sum of Price /2 then - 50)

I tried using IIf, but when I ran the report it just deleted my formula!!

Thanks in advance.

 

[retro]

Thanks!

I was on the right track, just trying to over-complicate things!!