Calculations on report...

[Newf via AccessMonster.com]

I am new in doing calculation in Access. I am using Access 2000 and need a
little help with If Then Staements using in the Control Source. Do I still
need to DIM anything?? Here is the code that i would like to use that I
thought I could use in Vb but an error is coming up. When I use the Control
Source do I have to refernce to either the query or table for the variable
that I am using or can I just use the name that I used for that field? I HOPE
someone can help moi. Thanks in advance.

Dim txt_RRSP As Integer
Dim txt_Totals As Integer
Dim rpt_txt_Fed_Tax As Integer
Dim txt_CFSA_Arrears_Tot As Integer
Dim txt_CFSA_Interest_Tot As Integer
Dim txt_CFPF_Arrears_Tot As Integer
Dim txt_CFPF_Interest_Tot As Integer

txt_CFSA_Interest = rpt_txt_CFSA_Arrears
rpt_txt_CFSA_Arrears_Tot = txt_CFSA_Interest
rpt_txt_CFSA_Interest = txt_CFSA_Interest
rpt_txt_CFSA_Interest_Tot = rpt_txt_CFSA_Interest
rpt_txt_CFPF_Arrears_Tot = txt_CFPF_Arrears
rpt_txt_CFPF_Interest_Tot = rpt_txt_CFPF_Late_Interest

txt_CFSA_Arrears_Tot = fCFSA_Arrears_Tot
txt_CFSA_Interest_Tot = fCFSA_Arrears_Tot
txt_CFPF_Arrears_Tot = fCFPF_Arrears_Tot
txt_CFPF_Interest_Tot = fCFSA_Arrears_Tot
rpt_txt_Totals = rTotals
txt_RRSP = fRRSP

rTotals = fCFSA_Arrears_Tot + fCFSA_Arrears_Tot + fCFPF_Arrears_Tot +
fCFSA_Arrears_Tot

If fRRSP <> 0 Then
rpt_txt_Fed_Tax = rTotals * 0.1
Else
rpt_txt_Fed_Tax = 0
End If


Newf
~Everyone needs a NEWFIE as a friend~

 

[Duane Hookom]

We don't know what you are attempting to accomplish. I think all of what you
are attempting to do can be done without any code.

If you really need code, then it is important to tell us which section and
event contains the code.

 

[Newf via AccessMonster.com]

Duane:
Thanks for quick response. There are a few fields on my report that I need
calcs done. Some need If then statements and not to familar with using that
in a control source field. I did have some of the fields linked to the query
by Control Source and that all work well. I had someone else tell me I should
use VB to do this. But no code would be perfect for this if it can be done.

So for this field in the query txt_RRSP. I have this calc'd in the Control
Source for this field:
rpt_txt_Totals = rpt_txt_CFSA_Arrears_Tot + rpt_txt_CFSA_Interest_Tot +
rpt_txt_CFPF_Arrears_Tot + rpt_txt_CFPF_Interest_Tot

In the Control Source for this - rpt_txt_Fed_Tax - I would like to have this:

If txt_RRSP<> 0 Then
rpt_txt_Fed_Tax = rpt_txt_Totals* 0.1
Else
rpt_txt_Fed_Tax = 0
End If

Newf

We don't know what you are attempting to accomplish. I think all of what you
are attempting to do can be done without any code.

If you really need code, then it is important to tell us which section and
event contains the code.

I am new in doing calculation in Access. I am using Access 2000 and need a
little help with If Then Staements using in the Control Source. Do I still

[quoted text clipped - 39 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Duane Hookom]

I'm still a little (maybe a lot) confused. "Fields" don't have a "Control
Source". "Controls" have a "Control Source".
I don't know if you are referencing fields or controls or whatever. Your use
of "rpt_txt_" suggests a text box name on a report (correct?).

You can use IIf() in a control source like:
=IIf( txt_RRSP<>0 , rpt_txtTotals* 0.1, 0)


--
Duane Hookom
MS Access MVP

Duane:
Thanks for quick response. There are a few fields on my report that I need
calcs done. Some need If then statements and not to familar with using
that
in a control source field. I did have some of the fields linked to the
query
by Control Source and that all work well. I had someone else tell me I
should
use VB to do this. But no code would be perfect for this if it can be
done.

So for this field in the query txt_RRSP. I have this calc'd in the Control
Source for this field:
rpt_txt_Totals = rpt_txt_CFSA_Arrears_Tot + rpt_txt_CFSA_Interest_Tot +
rpt_txt_CFPF_Arrears_Tot + rpt_txt_CFPF_Interest_Tot

In the Control Source for this - rpt_txt_Fed_Tax - I would like to have
this:

If txt_RRSP<> 0 Then
rpt_txt_Fed_Tax = rpt_txt_Totals* 0.1
Else
rpt_txt_Fed_Tax = 0
End If

Newf

We don't know what you are attempting to accomplish. I think all of what
you
are attempting to do can be done without any code.

If you really need code, then it is important to tell us which section and
event contains the code.

I am new in doing calculation in Access. I am using Access 2000 and need
a
little help with If Then Staements using in the Control Source. Do I
still

[quoted text clipped - 39 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Newf via AccessMonster.com]

Yes to the text box question being used on the report. So I guess the
=IIf( txt_RRSP<>0 , rpt_txtTotals* 0.1, 0)

So the (......*.1,0) the ,0 is the else part???

If so.. THANKS ALOT!!!

cw

I'm still a little (maybe a lot) confused. "Fields" don't have a "Control
Source". "Controls" have a "Control Source".
I don't know if you are referencing fields or controls or whatever. Your use
of "rpt_txt_" suggests a text box name on a report (correct?).

You can use IIf() in a control source like:

Duane:
Thanks for quick response. There are a few fields on my report that I need

[quoted text clipped - 36 lines]

 

[Duane Hookom]

Let us know if this doesn't work for you.

--
Duane Hookom
MS Access MVP

Yes to the text box question being used on the report. So I guess the
=IIf( txt_RRSP<>0 , rpt_txtTotals* 0.1, 0)

So the (......*.1,0) the ,0 is the else part???

If so.. THANKS ALOT!!!

cw

I'm still a little (maybe a lot) confused. "Fields" don't have a "Control
Source". "Controls" have a "Control Source".
I don't know if you are referencing fields or controls or whatever. Your
use
of "rpt_txt_" suggests a text box name on a report (correct?).

You can use IIf() in a control source like:

Duane:
Thanks for quick response. There are a few fields on my report that I
need

[quoted text clipped - 36 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Newf via AccessMonster.com]

Duane:
Thanks for the help. It worked PERFECT!!!

Now I have a little problem with a BIGGER If.. Then.. Else.. statement. I'm
not too familar with using the control source of a text box for If staements.
I hope you can help.

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 * .10
Endif
Else
rpt_txt_Fed_Tax = rpt_txt_Tot_ROEC * .10
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * 0
Endif
Endif


Also cannot I use a control source for one text box to get the values of
another????

Newf

Let us know if this doesn't work for you.

Yes to the text box question being used on the report. So I guess the
=IIf( txt_RRSP<>0 , rpt_txtTotals* 0.1, 0)

[quoted text clipped - 20 lines]

 

[Duane Hookom]

I'm not sure I would create an IIf() statement for this. It seems to me the
values in this expression may change over time so there should be a table of
rates based on Province and whatever. At the very least, I would create a
small user-defined function that accepts
frm_txt_RRSP
rpt_txt_Fed_Tax
rpt_txt_Que_Tax
rpt_txt_Tot_ROEC
frm_cmb_Province
and returns a value.

--
Duane Hookom
MS Access MVP

Duane:
Thanks for the help. It worked PERFECT!!!

Now I have a little problem with a BIGGER If.. Then.. Else.. statement.
I'm
not too familar with using the control source of a text box for If
staements.
I hope you can help.

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 * .10
Endif
Else
rpt_txt_Fed_Tax = rpt_txt_Tot_ROEC * .10
rpt_txt_Que_Tax = rpt_txt_Tot_ROEC * 0
Endif
Endif


Also cannot I use a control source for one text box to get the values of
another????

Newf

Let us know if this doesn't work for you.

Yes to the text box question being used on the report. So I guess the
=IIf( txt_RRSP<>0 , rpt_txtTotals* 0.1, 0)

[quoted text clipped - 20 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Newf via AccessMonster.com]

Duane:
I'm not sure what you mean with small user-defined function??? As for the
table way how can I go about doing this??

Newf

I'm not sure I would create an IIf() statement for this. It seems to me the
values in this expression may change over time so there should be a table of
rates based on Province and whatever. At the very least, I would create a
small user-defined function that accepts
frm_txt_RRSP
rpt_txt_Fed_Tax
rpt_txt_Que_Tax
rpt_txt_Tot_ROEC
frm_cmb_Province
and returns a value.

Duane:
Thanks for the help. It worked PERFECT!!!

[quoted text clipped - 39 lines]

 

[Duane Hookom]

A user-defined function is some code that you write and then use like you
would use almost any built-in function.
I am not sure of the logic of your If Else EndIf stuff. I would create a
small lookup table that would find the multiplier based on some values in
your tables.

--
Duane Hookom
MS Access MVP

Duane:
I'm not sure what you mean with small user-defined function??? As for the
table way how can I go about doing this??

Newf

I'm not sure I would create an IIf() statement for this. It seems to me
the
values in this expression may change over time so there should be a table
of
rates based on Province and whatever. At the very least, I would create a
small user-defined function that accepts
frm_txt_RRSP
rpt_txt_Fed_Tax
rpt_txt_Que_Tax
rpt_txt_Tot_ROEC
frm_cmb_Province
and returns a value.

Duane:
Thanks for the help. It worked PERFECT!!!

[quoted text clipped - 39 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Newf via AccessMonster.com]

Duane:
Thanks for advice but I'm still lost with what your saying to do. I am not
TOO familar with coding for calculations. Any help would be greatly
appreciated.

Newf

A user-defined function is some code that you write and then use like you
would use almost any built-in function.
I am not sure of the logic of your If Else EndIf stuff. I would create a
small lookup table that would find the multiplier based on some values in
your tables.

Duane:
I'm not sure what you mean with small user-defined function??? As for the

[quoted text clipped - 20 lines]

 

[Duane Hookom]

You do know your field and control names as well as your specifications. We
don't know these without guessing. Can you explain so some one can assist?

--
Duane Hookom
MS Access MVP

Duane:
Thanks for advice but I'm still lost with what your saying to do. I am not
TOO familar with coding for calculations. Any help would be greatly
appreciated.

Newf

A user-defined function is some code that you write and then use like you
would use almost any built-in function.
I am not sure of the logic of your If Else EndIf stuff. I would create a
small lookup table that would find the multiplier based on some values in
your tables.

Duane:
I'm not sure what you mean with small user-defined function??? As for
the

[quoted text clipped - 20 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Newf via AccessMonster.com]

This database is used to calculate what the person is owed from overpayment.

rpt_txt_Que_Tax = QUE TAX
rpt_txt_Fed_Tax = FED TAX
rpt_txt_Tot_ROEC = TOTAL


The first check is to see if there was an RRSP contribution. If there is a
contribution the FED TAX = TOTAL * 0. Then I need to check to see if PROVINCE
= QC. If it does the QC TAX = TOTAL * .16 and if not QC then QC TAX = TOTAL *
0.

If there was no contribution to RRSP made I have to check and see if
PROVINCE = QC. If so another check is to see if the TOTAL is > 5000 and if so
FED TAX = TOTAL * .10 and QUE TAX = TOTAL * .16 and if < 5000, FED TAX =
TOTAL * .05 and QUE TAX = TOTAL * .16. If PROVINCE <> QC then FED TAX = TOTAL
* .10 and QUE TAX = TOTAL * 0.

I hope this helps...

NEWF

You do know your field and control names as well as your specifications. We
don't know these without guessing. Can you explain so some one can assist?

Duane:
Thanks for advice but I'm still lost with what your saying to do. I am not

[quoted text clipped - 15 lines]

 

[Duane Hookom]

I would rather not guess which of these are controls on reports or fields in
a report's record source. Did you tell us how "to see if there was an RRSP
contribution"?

Generically, you could create a user-defined function by opening a new blank
module and entering something like:

Function GetQCTax(pstrProvince as String, _
dblTotal as Double) as Double
If pstrProvince = "QC" Then
GetQCTax = dblTotal * 0.16
Else
GetQCTax = 0
End If
End Function

Save this module as "modTaxCalcs". In your report, you can set a control
source of a text box to something like:
=GetQCTax([PROVINCE], [TOTAL])


--
Duane Hookom
MS Access MVP

This database is used to calculate what the person is owed from
overpayment.

rpt_txt_Que_Tax = QUE TAX
rpt_txt_Fed_Tax = FED TAX
rpt_txt_Tot_ROEC = TOTAL


The first check is to see if there was an RRSP contribution. If there is a
contribution the FED TAX = TOTAL * 0. Then I need to check to see if
PROVINCE
= QC. If it does the QC TAX = TOTAL * .16 and if not QC then QC TAX =
TOTAL *
0.

If there was no contribution to RRSP made I have to check and see if
PROVINCE = QC. If so another check is to see if the TOTAL is > 5000 and if
so
FED TAX = TOTAL * .10 and QUE TAX = TOTAL * .16 and if < 5000, FED TAX =
TOTAL * .05 and QUE TAX = TOTAL * .16. If PROVINCE <> QC then FED TAX =
TOTAL
* .10 and QUE TAX = TOTAL * 0.

I hope this helps...

NEWF

You do know your field and control names as well as your specifications.
We
don't know these without guessing. Can you explain so some one can assist?

Duane:
Thanks for advice but I'm still lost with what your saying to do. I am
not

[quoted text clipped - 15 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Newf via AccessMonster.com]

As in for RRSP it will be entered on the form by the user.

I would rather not guess which of these are controls on reports or fields in
a report's record source. Did you tell us how "to see if there was an RRSP
contribution"?

Generically, you could create a user-defined function by opening a new blank
module and entering something like:

Function GetQCTax(pstrProvince as String, _
dblTotal as Double) as Double
If pstrProvince = "QC" Then
GetQCTax = dblTotal * 0.16
Else
GetQCTax = 0
End If
End Function

Save this module as "modTaxCalcs". In your report, you can set a control
source of a text box to something like:
=GetQCTax([PROVINCE], [TOTAL])

This database is used to calculate what the person is owed from
overpayment.

[quoted text clipped - 32 lines]

 

[Duane Hookom]

Did you understand the function? Can you figure out how to create your own
functions? Is RRSP a bound control (stored in a table)?

Are you sure this is a reports question since you state RRSP is on a form?

--
Duane Hookom
MS Access MVP

As in for RRSP it will be entered on the form by the user.

I would rather not guess which of these are controls on reports or fields
in
a report's record source. Did you tell us how "to see if there was an RRSP
contribution"?

Generically, you could create a user-defined function by opening a new
blank
module and entering something like:

Function GetQCTax(pstrProvince as String, _
dblTotal as Double) as Double
If pstrProvince = "QC" Then
GetQCTax = dblTotal * 0.16
Else
GetQCTax = 0
End If
End Function

Save this module as "modTaxCalcs". In your report, you can set a control
source of a text box to something like:
=GetQCTax([PROVINCE], [TOTAL])

This database is used to calculate what the person is owed from
overpayment.

[quoted text clipped - 32 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Newf via AccessMonster.com]

Sorry about all of this. I should of mentioned in the beginning that I am a
REAL NEWBIE with calculations.

No I do not understand it fully. Reading it I can figure somewhat it means.
As in creating a function I guess I can read around to write them. Yes this
is a report problem because I need the calcultions to be shown on the report
and get some of the values that were entered to find other calcs. Or wouldn't
it be???

NEWF

Did you understand the function? Can you figure out how to create your own
functions? Is RRSP a bound control (stored in a table)?

Are you sure this is a reports question since you state RRSP is on a form?

As in for RRSP it will be entered on the form by the user.

[quoted text clipped - 25 lines]

 

[Duane Hookom]

I suggested how to create your function. I asked if RRSP is stored in a
table but you didn't answer. Is this a field in your report's record source?


--
Duane Hookom
MS Access MVP

Sorry about all of this. I should of mentioned in the beginning that I am
a
REAL NEWBIE with calculations.

No I do not understand it fully. Reading it I can figure somewhat it
means.
As in creating a function I guess I can read around to write them. Yes
this
is a report problem because I need the calcultions to be shown on the
report
and get some of the values that were entered to find other calcs. Or
wouldn't
it be???

NEWF

Did you understand the function? Can you figure out how to create your own
functions? Is RRSP a bound control (stored in a table)?

Are you sure this is a reports question since you state RRSP is on a form?

As in for RRSP it will be entered on the form by the user.

[quoted text clipped - 25 lines]

Newf
~Everyone needs a NEWFIE as a friend~

 

[Newf via AccessMonster.com]

Yes you did suggest but like I said I'm not sure how to. RRSP is stored in a
table since its a database and needs to be recalled later. The field is in
the QRY which is in the record source for the report.

NEWF

I suggested how to create your function. I asked if RRSP is stored in a
table but you didn't answer. Is this a field in your report's record source?

Sorry about all of this. I should of mentioned in the beginning that I am
a

[quoted text clipped - 22 lines]

 

[Duane Hookom]

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.

--
Duane Hookom
MS Access MVP

Yes you did suggest but like I said I'm not sure how to. RRSP is stored in
a
table since its a database and needs to be recalled later. The field is in
the QRY which is in the record source for the report.

NEWF

I suggested how to create your function. I asked if RRSP is stored in a
table but you didn't answer. Is this a field in your report's record
source?

Sorry about all of this. I should of mentioned in the beginning that I
am
a

[quoted text clipped - 22 lines]

Newf
~Everyone needs a NEWFIE as a friend~