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
Last edited by wrightie; 06-17-2009 at 09:26 AM.
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 "Star" 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.
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
Hi Dan,
I've attached an example for you.
Cheers
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
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?
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks