calculated control on form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have forgotten how to do calculated controls on forms and queries. What I
am doing are lead sheets that are faxed to salesmen. On this form, I have
Currency boxes for Commission Amount, Bonus and Total to be paid. I would
like the TotaltobePaid box to be calculated from the CommissionAmount and
Bonus fields. I then would like to add all of these fields to a query where
total amount of check is calculated.

How do I start?
 
I have forgotten how to do calculated controls on forms and queries. What I
am doing are lead sheets that are faxed to salesmen. On this form, I have
Currency boxes for Commission Amount, Bonus and Total to be paid. I would
like the TotaltobePaid box to be calculated from the CommissionAmount and
Bonus fields. I then would like to add all of these fields to a query where
total amount of check is calculated.

How do I start?
Click to expand...

If you want to both display the calculated values on the Form and
print them on the Report, the simplest approach is to do the
calculation right in the Query - and base both the form and report on
that Query.

I don't know how your CommissionAmount or Bonus are calculated, but
basically you'ld just open the query in design view; move to a vacant
Field cell; and type

CommissionAmount: [SalePrice] * 0.10

if (say) the commission is ten percent of SalePrice.

This field can then be used like any other field on the form or report
(though, of course, it cannot be updated).

John W. Vinson[MVP]
 
Thank you for your quick response. Your solution solved part of my problem.
Now how do I get a sum total for the Total to be Paid cell in the query.

My original question was how to put a calculated control on the form. I
have "Total to be paid" box on the table and form and need see the total
commission for each sale;the next box is TotalCheckAmount, which totals all
the commissions in the TotaltobePaid boxes. Thanks again.

John Vinson said:
I have forgotten how to do calculated controls on forms and queries. What I
am doing are lead sheets that are faxed to salesmen. On this form, I have
Currency boxes for Commission Amount, Bonus and Total to be paid. I would
like the TotaltobePaid box to be calculated from the CommissionAmount and
Bonus fields. I then would like to add all of these fields to a query where
total amount of check is calculated.

How do I start?
Click to expand...

If you want to both display the calculated values on the Form and
print them on the Report, the simplest approach is to do the
calculation right in the Query - and base both the form and report on
that Query.

I don't know how your CommissionAmount or Bonus are calculated, but
basically you'ld just open the query in design view; move to a vacant
Field cell; and type

CommissionAmount: [SalePrice] * 0.10

if (say) the commission is ten percent of SalePrice.

This field can then be used like any other field on the form or report
(though, of course, it cannot be updated).

John W. Vinson[MVP]
Click to expand...
 
Thank you for your quick response. Your solution solved part of my problem.
Now how do I get a sum total for the Total to be Paid cell in the query.

My original question was how to put a calculated control on the form. I
have "Total to be paid" box on the table and form and need see the total
commission for each sale;the next box is TotalCheckAmount, which totals all
the commissions in the TotaltobePaid boxes. Thanks again.
Click to expand...

I'm not certain of the structure of your table or form. Is Total to be
Paid calculated across fields in a record, or across records? If the
latter, do the totalling on the Form instead of in the query - each
detail record in the query should have the information for one row;
you can put a textbox on the form Footer with a control source

=Sum([ToBePaid])

to sum the values in the query field ToBePaid in the Form's
recordsource.

John W. Vinson[MVP]
 
Need more help with your answer. The "Total to be Paid" is per sale. This
is within each record. On the form, I also have a box for Total Amount of
Check. In this box, I would like the sum of all Total to be Paids for each
record. I would like toknow how to do this on the query and on the form.
Thank you.

John Vinson said:
Thank you for your quick response. Your solution solved part of my problem.
Now how do I get a sum total for the Total to be Paid cell in the query.

My original question was how to put a calculated control on the form. I
have "Total to be paid" box on the table and form and need see the total
commission for each sale;the next box is TotalCheckAmount, which totals all
the commissions in the TotaltobePaid boxes. Thanks again.
Click to expand...

I'm not certain of the structure of your table or form. Is Total to be
Paid calculated across fields in a record, or across records? If the
latter, do the totalling on the Form instead of in the query - each
detail record in the query should have the information for one row;
you can put a textbox on the form Footer with a control source

=Sum([ToBePaid])

to sum the values in the query field ToBePaid in the Form's
recordsource.

John W. Vinson[MVP]
Click to expand...
 
Need more help with your answer. The "Total to be Paid" is per sale. This
is within each record. On the form, I also have a box for Total Amount of
Check. In this box, I would like the sum of all Total to be Paids for each
record. I would like toknow how to do this on the query and on the form.
Thank you.
Click to expand...

Please explain what fields exist in your table, and how the two totals
are calculated.

John W. Vinson[MVP]
 
For each record, there is a commission, bonus, TotaltobePaidpersale and
TotCheck. The TotaltobePaid is a sum of the commission and bonus on each
record. The TotCheck is a sum of the TotaltobePaidpersales across all the
records in the query. I took TotCheck off the form because then it would
total all the records with commission and bonus instead of just the records
with sales. The query makes sure it includes just the sales. I don't know
how to do this on the form
 
For each record, there is a commission, bonus, TotaltobePaidpersale and
TotCheck. The TotaltobePaid is a sum of the commission and bonus on each
record. The TotCheck is a sum of the TotaltobePaidpersales across all the
records in the query. I took TotCheck off the form because then it would
total all the records with commission and bonus instead of just the records
with sales. The query makes sure it includes just the sales. I don't know
how to do this on the form
Click to expand...

Perhaps a DSum() call would work: set the Control Source of a textbox
(anywhere on the form, needn't be the footer) to

=DSum("[TotalToBePaidPerSales]", "[YourQueryName]", "[Sales] IS NOT
NULL")


The third argument is a text string which should contain the criteria
selecting which values should be summed.

John W. Vinson[MVP]
 
This helped somewhat in that it did sum the TotaltobePaid cells on the query
and on the form. However, what it also did was to put this figure
(TotalCheck) on every record in the form instead of just the records for that
week. It puts this figure in that space for every form record in the
database.

On the query, it puts this same figure (TotalCheck) after every record
displayed in the query, making it confusing to outside persons reading it.
Is there any way to have it display on a separate line by itself?

John Vinson said:
For each record, there is a commission, bonus, TotaltobePaidpersale and
TotCheck. The TotaltobePaid is a sum of the commission and bonus on each
record. The TotCheck is a sum of the TotaltobePaidpersales across all the
records in the query. I took TotCheck off the form because then it would
total all the records with commission and bonus instead of just the records
with sales. The query makes sure it includes just the sales. I don't know
how to do this on the form
Click to expand...

Perhaps a DSum() call would work: set the Control Source of a textbox
(anywhere on the form, needn't be the footer) to

=DSum("[TotalToBePaidPerSales]", "[YourQueryName]", "[Sales] IS NOT
NULL")


The third argument is a text string which should contain the criteria
selecting which values should be summed.

John W. Vinson[MVP]
Click to expand...
 
This helped somewhat in that it did sum the TotaltobePaid cells on the query
and on the form. However, what it also did was to put this figure
(TotalCheck) on every record in the form instead of just the records for that
week. It puts this figure in that space for every form record in the
database.

On the query, it puts this same figure (TotalCheck) after every record
displayed in the query, making it confusing to outside persons reading it.
Is there any way to have it display on a separate line by itself?
Click to expand...

You can put a textbox anywhere you like on the form, and you can set
its control source to any expression you like. I guess I don't
understand what you've done vs. what you want.

If people are looking at query datasheets - or even Datasheet view
forms - DON'T DO THAT. A Single or Continuous Form gives you much more
control (for one thing, you don't need to display every field on every
row!)

John W. Vinson[MVP]
 
Back
Top