Total from one subreport to another

[T-bird]

I have two subreports, “ETLsubrpt†& “ETLsubrpt1†in a main report.
“division†groups them both. On ETLsubrpt1, I want to calculate the
percentage the airfare fee is of the division total airfare. On one subrpt, I
have my “Total ticket Amt†in the group footer as:
=Sum([TICKET AMT])

On the other subrpt I have my “Total Service Fees†in the group footer as:
=([Total Service Fees]/[Reports]![Executive Travel
Summary]![ETLsubrpt].[Report]![Total Ticket Amt])

Please help me understand what I have wrong

 

[strive4peace]

Hi T-bird,

try this:

=([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])

if there will be occassions when ETLsubrpt has no data or
Total Ticket Amt is 0... here is a function you can use
from Arvin's website:

'~~~~~~~~~~~~~~~

Reports: Handle Null values in fields
Author(s)
Arvin Meyer

If you're using a field as part of an expression in another
control on the report, a Null value in the field can cause
errors.

An easy way to handle such errors is to force the field to
Zero using a custom function if it contains a Null value.
For example, I use the following function:

'******** Code Start *********
Function ErrorAvoid(n As Variant) As Variant
On Error GoTo Trap
ErrorAvoid = n
Exit Function
Trap:
ErrorAvoid = 0
Resume Next
Exit Function
End Function
'******** Code End *********

Then use it in a calculated text boc

=(txtAnyField)+ErrorAvoid(Reports!RptName!SubRptName.Report!txtSubtotal)

'~~~~~~~~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[strive4peace]

ps

the link to Arvin's code is:

http://www.mvps.org/access/reports/rpt0007.htm

here is another function written to do the same thing that
Arvin says he prefers to his method (although I did not test
it, I assume it would work for a report as well as a form):

'~~~~~~~~~~~~~~~`
http://www.mvps.org/access/forms/frm0022.htm
'~~~~~~~~~~~~~~~`

Forms: #Error when the Subform has no records
Author(s)
Keri Hardwick

Here are some facts about #ERROR returned when a subform has
no records:In these examples, [Subf field] refers to a
syntactically correctreference to a subform field from a
main form.

1. It will not evaluate to null: IsNull([Subf field]) is false

2. It will evaluate to an error on the main form, but not
when passeto a global module:
IsError([Subf field]) on the main form evaluates to true
IsAnError([Subf field]) returns false, where this
function exists in a global module:

Function IsAnError(testvalue as variant) as Boolean
IsAnError = IsError(testvalue)
End Function

3. It will not evaluate to numeric. IsNumeric([subf field])
evaluates to false.

I have found this to be the best test, since often it is a
total or other number being passed back to the main form. If
not, there is usually some numeric field on the sub that can
be tested whether or not it is the field used on the main form.

This test indicates when there are records, IsNumeric will
be true; when there are no records, IsNumeric will be false.
So, you can catch "no records" and display what you want
instead of #ERROR.

This function returns zero instead of #ERROR when used like
this:nnz([Subf field]) on the main form. I use it from a
global module.

'***************** Code Start ***************
'This code was originally written by Keri Hardwick.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Keri Hardwick
'
Function nnz(testvalue As Variant) As Variant
'Not Numeric return zero
If Not (IsNumeric(testvalue)) Then
nnz = 0
Else
nnz = testvalue
End If
End Function
'***************** Code End ****************


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hi T-bird,

try this:

=([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total Ticket Amt])

if there will be occassions when ETLsubrpt has no data or Total Ticket
Amt is 0... here is a function you can use from Arvin's website:

'~~~~~~~~~~~~~~~

Reports: Handle Null values in fields
Author(s)
Arvin Meyer

If you're using a field as part of an expression in another control on
the report, a Null value in the field can cause errors.

An easy way to handle such errors is to force the field to Zero using a
custom function if it contains a Null value. For example, I use the
following function:

'******** Code Start *********
Function ErrorAvoid(n As Variant) As Variant
On Error GoTo Trap
ErrorAvoid = n
Exit Function
Trap:
ErrorAvoid = 0
Resume Next
Exit Function
End Function
'******** Code End *********

Then use it in a calculated text boc

=(txtAnyField)+ErrorAvoid(Reports!RptName!SubRptName.Report!txtSubtotal)

'~~~~~~~~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I have two subreports, “ETLsubrpt†& “ETLsubrpt1†in a main report.
“division†groups them both. On ETLsubrpt1, I want to calculate the
percentage the airfare fee is of the division total airfare. On one
subrpt, I have my “Total ticket Amt†in the group footer as:
=Sum([TICKET AMT])

On the other subrpt I have my “Total Service Fees†in the group footer
as:
=([Total Service Fees]/[Reports]![Executive Travel
Summary]![ETLsubrpt].[Report]![Total Ticket Amt])

Please help me understand what I have wrong

 

[T-bird]

I tried this formula and it gives me the wrong precentage, it is taking the
percentage by dividind each divisions Total Service Feeby only one divisions
Total Ticket Amt and I'm not sure why? The data is such

TSF Pct TTA
CORPORATE SERVICES $5.00 0.04% $339.70
FINANCE & PLANNING $10.00 0.08% $663.80
IT DEPARTMENT $5.00 0.04% $471.20
LEGAL $20.00 0.16% $272.00
LIFE & ANNUITY $25.00 0.20% $1190.00
MARKETING $540.00 4.37% $36265.27
PROPERTY & CASUALTY $265.00 2.14% $12369.82

The text boxes are all located in the group footers....PLEASE HELP IF YOU CAN

Hi T-bird,

try this:

=([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])

if there will be occassions when ETLsubrpt has no data or
Total Ticket Amt is 0... here is a function you can use
from Arvin's website:

'~~~~~~~~~~~~~~~

Reports: Handle Null values in fields
Author(s)
Arvin Meyer

If you're using a field as part of an expression in another
control on the report, a Null value in the field can cause
errors.

An easy way to handle such errors is to force the field to
Zero using a custom function if it contains a Null value.
For example, I use the following function:

'******** Code Start *********
Function ErrorAvoid(n As Variant) As Variant
On Error GoTo Trap
ErrorAvoid = n
Exit Function
Trap:
ErrorAvoid = 0
Resume Next
Exit Function
End Function
'******** Code End *********

Then use it in a calculated text boc

=(txtAnyField)+ErrorAvoid(Reports!RptName!SubRptName.Report!txtSubtotal)

'~~~~~~~~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I have two subreports, “ETLsubrpt†& “ETLsubrpt1†in a main report.
“division†groups them both. On ETLsubrpt1, I want to calculate the
percentage the airfare fee is of the division total airfare. On one subrpt, I
have my “Total ticket Amt†in the group footer as:
=Sum([TICKET AMT])

On the other subrpt I have my “Total Service Fees†in the group footer as:
=([Total Service Fees]/[Reports]![Executive Travel
Summary]![ETLsubrpt].[Report]![Total Ticket Amt])

Please help me understand what I have wrong

 

[strive4peace]

Hi T-bird,

I have not tested the code in the links I gave you (just
assuming it works because of the authors)

but
=([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])

should work -- I do use that method myself. I prefer to use
Parent (relative referencing) instead of absolute referencing

I would take it a couple steps further to test to make sure
1. the subreport has data
2. the sum is not 0 (since dividing by zero is undefined)

=IIF(parent.[ETLsubrpt].[Report].HasData,
IIF(nz(parent.[ETLsubrpt].[Report].[Total Ticket Amt]) <> 0,
([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])
,0)
,0)

If you still have problems, check to make sure you are using
the control NAMES and not the ControlSources

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I tried this formula and it gives me the wrong precentage, it is taking the
percentage by dividind each divisions Total Service Feeby only one divisions
Total Ticket Amt and I'm not sure why? The data is such

TSF Pct TTA
CORPORATE SERVICES $5.00 0.04% $339.70
FINANCE & PLANNING $10.00 0.08% $663.80
IT DEPARTMENT $5.00 0.04% $471.20
LEGAL $20.00 0.16% $272.00
LIFE & ANNUITY $25.00 0.20% $1190.00
MARKETING $540.00 4.37% $36265.27
PROPERTY & CASUALTY $265.00 2.14% $12369.82

The text boxes are all located in the group footers....PLEASE HELP IF YOU CAN




:

Hi T-bird,

try this:

=([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])

if there will be occassions when ETLsubrpt has no data or
Total Ticket Amt is 0... here is a function you can use
from Arvin's website:

'~~~~~~~~~~~~~~~

Reports: Handle Null values in fields
Author(s)
Arvin Meyer

If you're using a field as part of an expression in another
control on the report, a Null value in the field can cause
errors.

An easy way to handle such errors is to force the field to
Zero using a custom function if it contains a Null value.
For example, I use the following function:

'******** Code Start *********
Function ErrorAvoid(n As Variant) As Variant
On Error GoTo Trap
ErrorAvoid = n
Exit Function
Trap:
ErrorAvoid = 0
Resume Next
Exit Function
End Function
'******** Code End *********

Then use it in a calculated text boc

=(txtAnyField)+ErrorAvoid(Reports!RptName!SubRptName.Report!txtSubtotal)

'~~~~~~~~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I have two subreports, “ETLsubrpt†& “ETLsubrpt1†in a main report.
“division†groups them both. On ETLsubrpt1, I want to calculate the
percentage the airfare fee is of the division total airfare. On one subrpt, I
have my “Total ticket Amt†in the group footer as:
=Sum([TICKET AMT])

On the other subrpt I have my “Total Service Fees†in the group footer as:
=([Total Service Fees]/[Reports]![Executive Travel
Summary]![ETLsubrpt].[Report]![Total Ticket Amt])

Please help me understand what I have wrong

 

[T-bird]

The subreport will never not have data, I have tried this but yet still it is
not giving me the correct precentages. It is dividion each divisions total
service fees by only one of the division total ticket amount? I don't
understand how or why. I have double checke the controls and everything is
correct. If someone anyone can please help!!!!

Hi T-bird,

I have not tested the code in the links I gave you (just
assuming it works because of the authors)

but
=([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])

should work -- I do use that method myself. I prefer to use
Parent (relative referencing) instead of absolute referencing

I would take it a couple steps further to test to make sure
1. the subreport has data
2. the sum is not 0 (since dividing by zero is undefined)

=IIF(parent.[ETLsubrpt].[Report].HasData,
IIF(nz(parent.[ETLsubrpt].[Report].[Total Ticket Amt]) <> 0,
([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])
,0)
,0)

If you still have problems, check to make sure you are using
the control NAMES and not the ControlSources

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I tried this formula and it gives me the wrong precentage, it is taking the
percentage by dividind each divisions Total Service Feeby only one divisions
Total Ticket Amt and I'm not sure why? The data is such

TSF Pct TTA
CORPORATE SERVICES $5.00 0.04% $339.70
FINANCE & PLANNING $10.00 0.08% $663.80
IT DEPARTMENT $5.00 0.04% $471.20
LEGAL $20.00 0.16% $272.00
LIFE & ANNUITY $25.00 0.20% $1190.00
MARKETING $540.00 4.37% $36265.27
PROPERTY & CASUALTY $265.00 2.14% $12369.82

The text boxes are all located in the group footers....PLEASE HELP IF YOU CAN




:

Hi T-bird,

try this:

=([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])

if there will be occassions when ETLsubrpt has no data or
Total Ticket Amt is 0... here is a function you can use
from Arvin's website:

'~~~~~~~~~~~~~~~

Reports: Handle Null values in fields
Author(s)
Arvin Meyer

If you're using a field as part of an expression in another
control on the report, a Null value in the field can cause
errors.

An easy way to handle such errors is to force the field to
Zero using a custom function if it contains a Null value.
For example, I use the following function:

'******** Code Start *********
Function ErrorAvoid(n As Variant) As Variant
On Error GoTo Trap
ErrorAvoid = n
Exit Function
Trap:
ErrorAvoid = 0
Resume Next
Exit Function
End Function
'******** Code End *********

Then use it in a calculated text boc

=(txtAnyField)+ErrorAvoid(Reports!RptName!SubRptName.Report!txtSubtotal)

'~~~~~~~~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

T-bird wrote:

I have two subreports, “ETLsubrpt†& “ETLsubrpt1†in a main report.
“division†groups them both. On ETLsubrpt1, I want to calculate the
percentage the airfare fee is of the division total airfare. On one subrpt, I
have my “Total ticket Amt†in the group footer as:
=Sum([TICKET AMT])

On the other subrpt I have my “Total Service Fees†in the group footer as:
=([Total Service Fees]/[Reports]![Executive Travel
Summary]![ETLsubrpt].[Report]![Total Ticket Amt])

Please help me understand what I have wrong

 

[strive4peace]

Hi T-bird,

if you sit down with a calculator, do you get something
different? Maybe your logic needs some changes

When you say it is not giving correct percentages, how far
are they off -- decimal places or whole numbers?

Perhaps [ETLsubrpt].[Report].[Total Ticket Amt]
is not the number you need to use to get your percentages...


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

The subreport will never not have data, I have tried this but yet still it is
not giving me the correct precentages. It is dividion each divisions total
service fees by only one of the division total ticket amount? I don't
understand how or why. I have double checke the controls and everything is
correct. If someone anyone can please help!!!!

:

Hi T-bird,

I have not tested the code in the links I gave you (just
assuming it works because of the authors)

but
=([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])

should work -- I do use that method myself. I prefer to use
Parent (relative referencing) instead of absolute referencing

I would take it a couple steps further to test to make sure
1. the subreport has data
2. the sum is not 0 (since dividing by zero is undefined)

=IIF(parent.[ETLsubrpt].[Report].HasData,
IIF(nz(parent.[ETLsubrpt].[Report].[Total Ticket Amt]) <> 0,
([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])
,0)
,0)

If you still have problems, check to make sure you are using
the control NAMES and not the ControlSources

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I tried this formula and it gives me the wrong precentage, it is taking the
percentage by dividind each divisions Total Service Feeby only one divisions
Total Ticket Amt and I'm not sure why? The data is such

TSF Pct TTA
CORPORATE SERVICES $5.00 0.04% $339.70
FINANCE & PLANNING $10.00 0.08% $663.80
IT DEPARTMENT $5.00 0.04% $471.20
LEGAL $20.00 0.16% $272.00
LIFE & ANNUITY $25.00 0.20% $1190.00
MARKETING $540.00 4.37% $36265.27
PROPERTY & CASUALTY $265.00 2.14% $12369.82

The text boxes are all located in the group footers....PLEASE HELP IF YOU CAN




:



Hi T-bird,

try this:

=([Total Service Fees]/parent.[ETLsubrpt].[Report].[Total
Ticket Amt])

if there will be occassions when ETLsubrpt has no data or
Total Ticket Amt is 0... here is a function you can use

from Arvin's website:

'~~~~~~~~~~~~~~~

Reports: Handle Null values in fields
Author(s)
Arvin Meyer

If you're using a field as part of an expression in another
control on the report, a Null value in the field can cause
errors.

An easy way to handle such errors is to force the field to
Zero using a custom function if it contains a Null value.
For example, I use the following function:

'******** Code Start *********
Function ErrorAvoid(n As Variant) As Variant
On Error GoTo Trap
ErrorAvoid = n
Exit Function
Trap:
ErrorAvoid = 0
Resume Next
Exit Function
End Function
'******** Code End *********

Then use it in a calculated text boc

=(txtAnyField)+ErrorAvoid(Reports!RptName!SubRptName.Report!txtSubtotal)

'~~~~~~~~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

T-bird wrote:


I have two subreports, “ETLsubrpt†& “ETLsubrpt1†in a main report.
“division†groups them both. On ETLsubrpt1, I want to calculate the
percentage the airfare fee is of the division total airfare. On one subrpt, I
have my “Total ticket Amt†in the group footer as:
=Sum([TICKET AMT])

On the other subrpt I have my “Total Service Fees†in the group footer as:
=([Total Service Fees]/[Reports]![Executive Travel
Summary]![ETLsubrpt].[Report]![Total Ticket Amt])

Please help me understand what I have wrong