Help needed with formula in report. Was Help with code needed!

I am trying to figure out some code for a report issue;

I have an employee who has a incentive scheme.

I have a report showing various income amounts and in the report footer I vae totals for these feilds =sum([XXX]) these work fine.
I also have in the detail feild Overall text boxes for the three feilds concerned
I then have in the report footer =[xx]+[xx]+[xx] which give me a grand total for the report also works fine.

I now need to have a more complex formula which alows for;

if the grand total is greater than 8333 then subtract from the grand total 8333 then divide the new total by 2
I have this formula in excel and it is written =if(total>8333,total-8333)/2

Any help with this will be greatly appreciated!

Bob

re: Help needed with formula in report. Was Help with code needed!

Please remember to provide a meaningful Title for any threads you start! This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions. It is difficult for the experts to answer questions when there is not enough details provided to understand the problem.

Please take the time to read the Posting Guidelines, paying particular attention to Give a clear title to your question.

http://www.thescripts.com/forum/thread559246.html


Thank you and Welcome to TheScripts!!

Linq ;0)>

re: Help needed with formula in report. Was Help with code needed!

If the grand total is not greater than 8333, what are you going to do with control?

Something like this:

= IIf(Me.total > 8333, (total - 8333) / 2, "N/A")

will do the math if the total is over 8333, and place "N/A" in the control if if the total is not over 8333.

Linq ;0)>

re: Help needed with formula in report. Was Help with code needed!

will leave at 0 or leave blank

re: Help needed with formula in report. Was Help with code needed!

O.K. this should do:

= IIf(Me.total > 8333, (total - 8333) / 2, 0)

Linq ;0)>

re: Help needed with formula in report. Was Help with code needed!

Tried this thanks but I am being asked to enter a Parameter value for Me.total?

re: Help needed with formula in report. Was Help with code needed!

I'm sorry, that was the name I used in checking the code. Replace Me.Total with your total, if that's the actual name you're using.

= IIf(total > 8333, (total - 8333) / 2, 0)

Linq ;0)>

re: Help needed with formula in report. Was Help with code needed!

Ah-Ha!

Could you help me with something else please?

On my form I have a:

Purchase Amount Purchase Commision Commission Amount
Refinance Amount Refinance Commission
Top Up Amount Top Up Commission

Where I enter the amount, I then tick the appropriate commision amount and the Purchase, Refinance or Top Up Commission amount are calculated to those areas automatically.

I now have the problem that when I go to Reports, i insert a text box in the detail area and associate with the form Purchase, Refianance or top up for a control source but when I preview the report that area is blank on the report.
Do I have a setting problem is is it not possible to report on calculated totals?

Regards,

Bob