Calculations on report...

[Newf via AccessMonster.com]

Duane:
Thanks. Here are the fields that are on the form that the user enters. These
fields can be null but they will not be all null.
FORM - entered by user
frm_txt_CFSA_Arrears - can be null
frm_txt_CFSA_Interest - can be null
frm_txt_CFPF_Arrears - can be null
frm_txt_CFPF_Interest - can be null
frm_txt_PBDA_CFSA - can be null
frm_txt_PBDA_CFPF - can be null
frm_txt_RRSP - can be null
frm_txt_GAPDA - can be null
frm_cmb_Province - user selects this on form

REPORT
rpt_txt_Totals = the totals of 4 fields (CFSA & CFPF) - calc working OK
rpt_txt_Tot_ROEC = rpt_txt_Totals
rpt_txt_Que_Tax is determined by ROEC

Here is the if statement I need to place on the report either in the on open
event of from a push button (PRINT) on my form.
If frm_txt_RRSP <> 0 Then
rpt_txt_Fed_Tax = rpt_txt_Tot_ROEC * 0
If frm_cmb_Province = "QC" Then
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * .16
Else
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * .0
Endif
Else
If frm_cmb_Province = "QC" Then
If rpt_txt_ROEC > 5000 Then
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * .16
rpt_txt_Fed_Tax = rpt_txt_Tot_ROEC * .10
Else
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * .16
rpt_txt_Fed_Tax = rpt_txt_Tot_ROEC * .05
Endif
Else
rpt_txt_Fed_Tax = rpt_txt_Tot_ROEC * .10
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * 0
Endif
Endif

******SAMPLE*******
-------1--------
frm_txt_RSSP = 0.00
rpt_txt_Tot_ROEC = 1266.25
frm_cmb_Province = NL
(rpt_txt_Fed_Tax) 1266.25 * .10 = 126.63
(rpt_txt_Que_Tax) 1266.25 * 0 = 0


-------2----------
frm_txt_RRSP = 145.00
rpt_txt_Tot_ROEC = 466.45
frm_cmb_Province = QC
(rpt_txt_Fed_Tax) 466.45 * 0 = 0
(rpt_txt_Que_Tax) 466.45 * .16 = 74.63

------3---------
frm_txt_RRSP = 0.00
rpt_txt_Tot_ROEC = 5800.00
frm_cmb_Province = QC
(rpt_txt_Que_Tax) 5800.00 * .16 = 928.00
(rpt_txt_Fed_Tax) 5800.00 * .10 = 580.00


I hope this helps...

NEWF

Ok, tell us what the fields are from your report's record source, what
values need to be calculated, and how they are calculated. Some sample
records with calculated results would help us determine if we get the calcs
correct.

Yes you did suggest but like I said I'm not sure how to. RRSP is stored in
a

[quoted text clipped - 13 lines]

 

[Duane Hookom]

I thought I asked for the names of the fields in your report's record
source. You provided the "fields that are on the form that the user enters".
Are they the same? Do you have a field named "FORM"? Do some of your field
names begin with "frm_txt_"? You really have an old guy chasing his tail
since "frm" is generally a prefix given to form names and "txt" is generally
a prefix given to text box names.

You state frm_cmb_Province is selected on a form. Does this mean it isn't
bound to a field in a table or query?

--
Duane Hookom
MS Access MVP

Duane:
Thanks. Here are the fields that are on the form that the user enters.
These
fields can be null but they will not be all null.
FORM - entered by user
frm_txt_CFSA_Arrears - can be null
frm_txt_CFSA_Interest - can be null
frm_txt_CFPF_Arrears - can be null
frm_txt_CFPF_Interest - can be null
frm_txt_PBDA_CFSA - can be null
frm_txt_PBDA_CFPF - can be null
frm_txt_RRSP - can be null
frm_txt_GAPDA - can be null
frm_cmb_Province - user selects this on form

REPORT
rpt_txt_Totals = the totals of 4 fields (CFSA & CFPF) - calc working OK
rpt_txt_Tot_ROEC = rpt_txt_Totals
rpt_txt_Que_Tax is determined by ROEC

Here is the if statement I need to place on the report either in the on
open
event of from a push button (PRINT) on my form.
If frm_txt_RRSP <> 0 Then
rpt_txt_Fed_Tax = rpt_txt_Tot_ROEC * 0
If frm_cmb_Province = "QC" Then
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * .16
Else
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * .0
Endif
Else
If frm_cmb_Province = "QC" Then
If rpt_txt_ROEC > 5000 Then
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * .16
rpt_txt_Fed_Tax = rpt_txt_Tot_ROEC * .10
Else
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * .16
rpt_txt_Fed_Tax = rpt_txt_Tot_ROEC * .05
Endif
Else
rpt_txt_Fed_Tax = rpt_txt_Tot_ROEC * .10
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * 0
Endif
Endif

******SAMPLE*******
-------1--------
frm_txt_RSSP = 0.00
rpt_txt_Tot_ROEC = 1266.25
frm_cmb_Province = NL
(rpt_txt_Fed_Tax) 1266.25 * .10 = 126.63
(rpt_txt_Que_Tax) 1266.25 * 0 = 0


-------2----------
frm_txt_RRSP = 145.00
rpt_txt_Tot_ROEC = 466.45
frm_cmb_Province = QC
(rpt_txt_Fed_Tax) 466.45 * 0 = 0
(rpt_txt_Que_Tax) 466.45 * .16 = 74.63

------3---------
frm_txt_RRSP = 0.00
rpt_txt_Tot_ROEC = 5800.00
frm_cmb_Province = QC
(rpt_txt_Que_Tax) 5800.00 * .16 = 928.00
(rpt_txt_Fed_Tax) 5800.00 * .10 = 580.00


I hope this helps...

NEWF

Ok, tell us what the fields are from your report's record source, what
values need to be calculated, and how they are calculated. Some sample
records with calculated results would help us determine if we get the
calcs
correct.

Yes you did suggest but like I said I'm not sure how to. RRSP is stored
in
a

[quoted text clipped - 13 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Newf via AccessMonster.com]

Duane:
They are the same. No field named FORM. As for the names frm_txt would mean
that its a text on the form.
frm_cmb_Province is a field that is bound to a table on the form.

NEWF

I thought I asked for the names of the fields in your report's record
source. You provided the "fields that are on the form that the user enters".
Are they the same? Do you have a field named "FORM"? Do some of your field
names begin with "frm_txt_"? You really have an old guy chasing his tail
since "frm" is generally a prefix given to form names and "txt" is generally
a prefix given to text box names.

You state frm_cmb_Province is selected on a form. Does this mean it isn't
bound to a field in a table or query?

Duane:
Thanks. Here are the fields that are on the form that the user enters.

[quoted text clipped - 79 lines]

 

[Duane Hookom]

You don't seem to be understanding what I am asking. When you look at the
Field List while in the report design view, what fields do you see?

Forms display values from tables/queries. I am confused regarding:
"frm_cmb_Province is a field that is bound to a table on the form"
Does this mean you have a table with a field named "frm_cmb_Province"? Or,
do you have a field named "Province" in a table?


--
Duane Hookom
MS Access MVP

Duane:
They are the same. No field named FORM. As for the names frm_txt would
mean
that its a text on the form.
frm_cmb_Province is a field that is bound to a table on the form.

NEWF

I thought I asked for the names of the fields in your report's record
source. You provided the "fields that are on the form that the user
enters".
Are they the same? Do you have a field named "FORM"? Do some of your field
names begin with "frm_txt_"? You really have an old guy chasing his tail
since "frm" is generally a prefix given to form names and "txt" is
generally
a prefix given to text box names.

You state frm_cmb_Province is selected on a form. Does this mean it isn't
bound to a field in a table or query?

Duane:
Thanks. Here are the fields that are on the form that the user enters.

[quoted text clipped - 79 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Newf via AccessMonster.com]

Duane:
Why would you need to see all the fields on the report? There are a few other
fields that have no connection to the calculation at all. What I gave you is
what is needed. The fields on the report would be rpt_***_*******.

As for the frm_cmb_Province, thats is a combo box on the form which is
connected to a table. The field name in the table is Province. Form name is
frm_cmb_Province.

NEWF

You don't seem to be understanding what I am asking. When you look at the
Field List while in the report design view, what fields do you see?

Forms display values from tables/queries. I am confused regarding:
"frm_cmb_Province is a field that is bound to a table on the form"
Does this mean you have a table with a field named "frm_cmb_Province"? Or,
do you have a field named "Province" in a table?

Duane:
They are the same. No field named FORM. As for the names frm_txt would

[quoted text clipped - 21 lines]

 

[Duane Hookom]

Ok, so if I view the field list of your report I will see fields named
"rpt_..."? I'm not asking anything at this point regarding any controls such
as text boxes or anything else. If I open the datasheet view of your
report's record source, I will see column headings like "rpt_..."?

--
Duane Hookom
MS Access MVP

Duane:
Why would you need to see all the fields on the report? There are a few
other
fields that have no connection to the calculation at all. What I gave you
is
what is needed. The fields on the report would be rpt_***_*******.

As for the frm_cmb_Province, thats is a combo box on the form which is
connected to a table. The field name in the table is Province. Form name
is
frm_cmb_Province.

NEWF

You don't seem to be understanding what I am asking. When you look at the
Field List while in the report design view, what fields do you see?

Forms display values from tables/queries. I am confused regarding:
"frm_cmb_Province is a field that is bound to a table on the form"
Does this mean you have a table with a field named "frm_cmb_Province"? Or,
do you have a field named "Province" in a table?

Duane:
They are the same. No field named FORM. As for the names frm_txt would

[quoted text clipped - 21 lines]

Newf
~Everyone needs a NEWFIE as a friend~