Please Register to Remove these Ads

Hi,

This is driving me insane. Hoping someone can save me !

I have a report and within the report I have some calcualtions in some textboxes.
What I want to know is can I calculate this field that has
=[PremBal]*[Comm]/100 in the control source.

Any help is much appreciated.

Wrightie

I am not sure I am totally following you. You can do all kinda of calculations, however on a report sometimes you need to do them and either have vba replace the data in the "control source field" or you can have vba calculate and replace the data.

Without knowing more, not sure how else I can help.

Dan

__________________
"I am not a rocket scientist, I am a nuclear engineer." - Split_atom18
If my advice has been helpful to you, then please help me by clicking on the scales and adding to my reputation, Thanks!

Hi Dan,

Sorry for being vague !

What I have is 3 fields PremA, PremB & PremC which all have amounts in them. I have then created a textbox [PremBal] to sum these fields Control Source: =[PremA]+[PremB]+[PremC]
That all works fine

I also have a Comm field in my table which is a percentage (this varies but lets say for this one it's 10%) so what I've done is added another text field called CommBal and put this in
Control source: =[PremBal]*[Commission]/100
That all works fine.

So it looks like this
Premium Comm
500.00
500.00
200.00
1200.00 120.00

Now I need to sum these fields in my footer the PremA B & C works fine using
Control Source: =Sum([PremA]+[PremB]+[PremC])

This breaks per premium.

But I just can't get the CommBal field to sum i've tried these
=Sum([PremA]+[PremB]+[PremC])*[Comm]/100
=Sum([CommBal])

Arrgh it's driving me mad

I have seen you refer to the "commission" field above in 2 different ways. comm and commission. Is it possible they are getting mixed up?

Also if I am understanding this correct for each record there is a possibility of 3 different premiums, do they each have their own commission? or is there only one commission percentage per record.

__________________
"I am not a rocket scientist, I am a nuclear engineer." - Split_atom18
If my advice has been helpful to you, then please help me by clicking on the scales and adding to my reputation, Thanks!

Hi,

No the commission field is fine, i've just made a typo in my previous response.

Yeh each record has three different premium amounts and has only one commission percentage per record.

I hope that makes it a bit clearer for you.

Thanks again

Try:

=([PremA]+[PremB]+[PremC])*[Comm]/100

or whatever your comm field is

You were doubling up Sum(field + field)

Also if you have an example file you can zip and upload that would help.

Hope this helps,

Dan

__________________
"I am not a rocket scientist, I am a nuclear engineer." - Split_atom18
If my advice has been helpful to you, then please help me by clicking on the scales and adding to my reputation, Thanks!

Hi Dan,

I've attached an example for you.

Cheers

Attached Files

Example.zip (817.4 KB, 5 views)

Here is your sample modified. There were two small issues that I noticed. First, the final formula referenced calculated textbox names. You have to use the formula that's within those in your final formula - keep in mind, calculations refelct field names from your recordset, not textboxes, so Access will get confused if you try to calculate on a calculated textbox. Secondly, you just needed to sum those up - the formula as it stood only looked at the last record on each page --- =Sum(some_calculations).

Brent

Attached Files

Example.zip (32.2 KB, 13 views)

Oh my god, thank you so much Brent.

You've made my day. What a fool I am.

Bhill beat me, I finally got a chance to look at it. I do however have a question for you. Why do you have your commission % in 2750 for example then divide by 100 later?

__________________
"I am not a rocket scientist, I am a nuclear engineer." - Split_atom18
If my advice has been helpful to you, then please help me by clicking on the scales and adding to my reputation, Thanks!

Sorry if you wasted your time, Dan. I think my day starts an hour earlier than yours!

Na, is all good, didn't take but a second after I opened it up. No worries!

__________________
"I am not a rocket scientist, I am a nuclear engineer." - Split_atom18
If my advice has been helpful to you, then please help me by clicking on the scales and adding to my reputation, Thanks!