Summing IIF Statements in form footer

[DrMojo]

could someone please help me i am trying to get a total of a iif
statement and all i return is an #error.

here is what i have so far in a nut shell.

in order for me to get the number i need in the field "SupBonus" i
have its control source set to: "=IIf([TotalSales]>=[TempField],25,0)
" and if you look at that you will see the field "TempField" and the
temp fields control source is set to: "=[BudgetedNumberofSales]" and
then you go and look at the "BudgetedNumberofSales" field and you see
the control source of that is set to
"=([TotalHoursPoss]*[PercentField])*0.27" and then when you are
looking at that you see that the field name "PercentField" and here is
where we come into the trouble. i have to leave the percent field
changable, one week it my be 90% the next week it might be 80% and
that has to be easy to get to and change. i don't know of any way to
put this on a query to make the percent field percent changable with
out going into the query to have change it everytime it needs to be
changed. the main problem is on the form footer i need it to show me
the total $25 for the week or what ever date range i put in.
but every time i try to sum it, it gives me an #error.

this is the best way i can explain it.
any help would be great.
thanks
drmojo

 

[Guest]

It's late but it sounds like you have two problems - how to sum a calculated control and how to have a variable rate. I don't have enough information to answer the variable problem and the answer to the first question is easy, you can't. You need to repeat the calculation in the Sum() function. So instead of
=Sum(SomeCalculatedField)
You need to repeat the calcualtion as:
=Sum(fld2 * fld3 * (fldA + fldB) / 44)

 

[DrMojo]

i made it past that problem by using a module. now my problem is how
do i go about getting sum of the fields based off a function.

example:

field name: supbonus
control source: =GetBonus([SPH])

form footer:
field name:Sum_spBonus
control source: =Sum(GetBonus([SPH]))

when i do that it gives me an #error

any reason why that would be happening.

thanks

It's late but it sounds like you have two problems - how to sum a calculated control and how to have a variable rate. I don't have enough information to answer the variable problem and the answer to the first question is easy, you can't. You need to repeat the calculation in the Sum() function. So instead of
=Sum(SomeCalculatedField)
You need to repeat the calcualtion as:
=Sum(fld2 * fld3 * (fldA + fldB) / 44)
--
Bridge Players know all the Tricks

could someone please help me i am trying to get a total of a iif
statement and all i return is an #error.

here is what i have so far in a nut shell.

in order for me to get the number i need in the field "SupBonus" i
have its control source set to: "=IIf([TotalSales]>=[TempField],25,0)
" and if you look at that you will see the field "TempField" and the
temp fields control source is set to: "=[BudgetedNumberofSales]" and
then you go and look at the "BudgetedNumberofSales" field and you see
the control source of that is set to
"=([TotalHoursPoss]*[PercentField])*0.27" and then when you are
looking at that you see that the field name "PercentField" and here is
where we come into the trouble. i have to leave the percent field
changable, one week it my be 90% the next week it might be 80% and
that has to be easy to get to and change. i don't know of any way to
put this on a query to make the percent field percent changable with
out going into the query to have change it everytime it needs to be
changed. the main problem is on the form footer i need it to show me
the total $25 for the week or what ever date range i put in.
but every time i try to sum it, it gives me an #error.

this is the best way i can explain it.
any help would be great.
thanks
drmojo

 

[Guest]

Make sure that GetBonus() is returning an appropriate numeric data type such as double, currency, or Integer.
Make sure that GetBonus() is defined as public in a standard class module. It can't be in a form or report class module.
--
Bridge Players know all the Tricks

i made it past that problem by using a module. now my problem is how
do i go about getting sum of the fields based off a function.

example:

field name: supbonus
control source: =GetBonus([SPH])

form footer:
field name:Sum_spBonus
control source: =Sum(GetBonus([SPH]))

when i do that it gives me an #error

any reason why that would be happening.

thanks

It's late but it sounds like you have two problems - how to sum a calculated control and how to have a variable rate. I don't have enough information to answer the variable problem and the answer to the first question is easy, you can't. You need to repeat the calculation in the Sum() function. So instead of
=Sum(SomeCalculatedField)
You need to repeat the calcualtion as:
=Sum(fld2 * fld3 * (fldA + fldB) / 44)
--
Bridge Players know all the Tricks

could someone please help me i am trying to get a total of a iif
statement and all i return is an #error.

here is what i have so far in a nut shell.

in order for me to get the number i need in the field "SupBonus" i
have its control source set to: "=IIf([TotalSales]>=[TempField],25,0)
" and if you look at that you will see the field "TempField" and the
temp fields control source is set to: "=[BudgetedNumberofSales]" and
then you go and look at the "BudgetedNumberofSales" field and you see
the control source of that is set to
"=([TotalHoursPoss]*[PercentField])*0.27" and then when you are
looking at that you see that the field name "PercentField" and here is
where we come into the trouble. i have to leave the percent field
changable, one week it my be 90% the next week it might be 80% and
that has to be easy to get to and change. i don't know of any way to
put this on a query to make the percent field percent changable with
out going into the query to have change it everytime it needs to be
changed. the main problem is on the form footer i need it to show me
the total $25 for the week or what ever date range i put in.
but every time i try to sum it, it gives me an #error.

this is the best way i can explain it.
any help would be great.
thanks
drmojo