For Marshall Barton

[znibk]

Marshal,

Your answer to Margaret on 10/17/04 was so well explained, I hope you can
help me out. I’ve been trying for over three weeks to find the solution to
this problem: the Ending Balance of a report with two subreports.

I have the Beginning Balance and the two subreports in the Detail Section of
the Main Report: rpt_AFinancialStmt. I know that I have to add the beginning
balance, the total income together and subtract the total expenses to obtain
the ending balance.

I put an unbound text box in the Detail Section below the SubRpt_AInc in
which I put [ABegBal]+[ATotInc]. I received a post from Duane stating I
needed to have the name of the report and “txtIncomeSum†or “txtExpenseSumâ€
as the Control Source in an unbound text box. I worked with him but have no
end result and I have not heard from him in several days.

I was reading other posts and found the one mentioned above from Margaret,
Calucating Rpt/SubRpt, 10/17/04 and decided I’d post my question with your
name and hopefully you would see it and be able to help me reach achieve an
Ending Balance.

Not fully understanding your post, “If the subreport is in the main report's
detail section, then you need to calculate the total from all instances of
the subreport. This is done by adding a text box named
txtRunAmt to the detail section. Set its control source
expression to:

=IIf(QRYsubrptInvoice.REPORT!HasData,QRYsubrptInvoice.REPORT!idamt,0) and
set its RunningSum property to Over All.

Then the grand total in the main report's footer would use
the expression:
=Sum(amt) + txtRunAmt “


Since I’d tried numerous combinations, in the Detail Section with no
results, I decided to try and interpret what you’d written. I put an unbound
txt box with the control source of
“=IIf(SubRpt_AInc.Report!HasData,SubRpt_AInc.Report!idamt,0) and set the
running sum to all over. And try an unbound txt box in the report footer with
“=Sum(amt) + txtRunAmtâ€

After all, what I’d been through, I did not have anything to loose.
Unfortunately for me, it did not work. When I ran the report, it asked for
SubRpt_AInc. And =Sum(Amt) and txtRunAmt.

Marshall, I really hope you can help me as I have the other half of the
financial statement to create. I never dreamed it would be such an ordeal to
achieve an Ending Balance. I shall be humbled again and truly thankful if you
can help me. k

And, not knowing exactly what you want when trying to help someone, I hope
the following will help you more fully understand what I have. Incidentally,
the beginning balance, total income, and total expenses, are all correct and
line up perfectly as a Financial Report sould.
Now, how do I get the ending balance to show and be accurate with the
following information:

I have a main report, rpt_FinancialStatement. I have the Beginning Balance
and two subreports, SubReport_Income and SubReport_Expenses in the Detail
Section of the main report.

I’m beginning with descriptions of my subreports and will try to put them
in, a manner which makes sense

Name: SubRpt_ArvestIncome
Control Source: SumQry_ArvestIncome
SubRpt_ArvestIncome Report Footer:
Name: SubRpt_ArvestIncome.TxtIncomeSum
Control Source: =Sum([ATotInc])

Name: SubRpt_ArvestClientAndNonClientExpenses
Control Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_ArvestExpenses Report Footer:
Name: SubRpt_ArvestClientAndNonClientExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStatement
Control Source: Qry_Arvest

Detail Section of Rpt_FinancialStatement:
1st Field is:
Name: rpt_FinancialStatement.Report.TxtBegBalSum
Control Source: ABegBal

1st SubRpt is:
Name: SubRpt_ArvestIncome.Report.TxtIncomeSum
Source Object: Report.SubRpt_ArvestIncome
Link Child Field MonthYear
Link Master Field: MonthYear

2nd SubReport is:
Name: SubRpt_ArvestClientAndNonClientExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_ArvestClientAndNonClientExpenses
Link Child Field MonthYear
Link Master Field: MonthYear

Rpt_FinancialStatement Footer
In an unbound txt box, I tried the following two Control Sources:

=[ABegBal]+[SubRpt_AInc.Report.TxtIncomeSum]-
[SubRpt_AExpenses.Report.TxtExpenseSum]

AND
=Sum([ABegBal])+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]

Plus my interpretation of your post to Margaret.

As I stated above, these Names returned nothing; trying the Control Sources
themselves returned nothing except bracketing errors.

Good luck and Christ’s blessings,
k

 

[Marshall Barton]

znibk wrote

Your answer to Margaret on 10/17/04 was so well explained, I hope you can
help me out. I’ve been trying for over three weeks to find the solution to
this problem: the Ending Balance of a report with two subreports.

I have the Beginning Balance and the two subreports in the Detail Section of
the Main Report: rpt_AFinancialStmt. I know that I have to add the beginning
balance, the total income together and subtract the total expenses to obtain
the ending balance.

I put an unbound text box in the Detail Section below the SubRpt_AInc in
which I put [ABegBal]+[ATotInc]. I received a post from Duane stating I
needed to have the name of the report and “txtIncomeSum” or “txtExpenseSum”
as the Control Source in an unbound text box. I worked with him but have no
end result and I have not heard from him in several days.

I was reading other posts and found the one mentioned above from Margaret,
Calucating Rpt/SubRpt, 10/17/04 and decided I’d post my question with your
name and hopefully you would see it and be able to help me reach achieve an
Ending Balance.

Not fully understanding your post, “If the subreport is in the main report's
detail section, then you need to calculate the total from all instances of
the subreport. This is done by adding a text box named
txtRunAmt to the detail section. Set its control source
expression to:

=IIf(QRYsubrptInvoice.REPORT!HasData,QRYsubrptInvoice.REPORT!idamt,0) and
set its RunningSum property to Over All.

Then the grand total in the main report's footer would use
the expression:
=Sum(amt) + txtRunAmt “


Since I’d tried numerous combinations, in the Detail Section with no
results, I decided to try and interpret what you’d written. I put an unbound
txt box with the control source of
“=IIf(SubRpt_AInc.Report!HasData,SubRpt_AInc.Report!idamt,0) and set the
running sum to all over. And try an unbound txt box in the report footer with
“=Sum(amt) + txtRunAmt”

After all, what I’d been through, I did not have anything to loose.
Unfortunately for me, it did not work. When I ran the report, it asked for
SubRpt_AInc. And =Sum(Amt) and txtRunAmt.

Marshall, I really hope you can help me as I have the other half of the
financial statement to create. I never dreamed it would be such an ordeal to
achieve an Ending Balance. I shall be humbled again and truly thankful if you
can help me. k

And, not knowing exactly what you want when trying to help someone, I hope
the following will help you more fully understand what I have. Incidentally,
the beginning balance, total income, and total expenses, are all correct and
line up perfectly as a Financial Report sould.
Now, how do I get the ending balance to show and be accurate with the
following information:

I have a main report, rpt_FinancialStatement. I have the Beginning Balance
and two subreports, SubReport_Income and SubReport_Expenses in the Detail
Section of the main report.

I’m beginning with descriptions of my subreports and will try to put them
in, a manner which makes sense

Name: SubRpt_ArvestIncome
Control Source: SumQry_ArvestIncome
SubRpt_ArvestIncome Report Footer:
Name: SubRpt_ArvestIncome.TxtIncomeSum
Control Source: =Sum([ATotInc])

Name: SubRpt_ArvestClientAndNonClientExpenses
Control Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_ArvestExpenses Report Footer:
Name: SubRpt_ArvestClientAndNonClientExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStatement
Control Source: Qry_Arvest

Detail Section of Rpt_FinancialStatement:
1st Field is:
Name: rpt_FinancialStatement.Report.TxtBegBalSum
Control Source: ABegBal

1st SubRpt is:
Name: SubRpt_ArvestIncome.Report.TxtIncomeSum
Source Object: Report.SubRpt_ArvestIncome
Link Child Field MonthYear
Link Master Field: MonthYear

2nd SubReport is:
Name: SubRpt_ArvestClientAndNonClientExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_ArvestClientAndNonClientExpenses
Link Child Field MonthYear
Link Master Field: MonthYear

Rpt_FinancialStatement Footer
In an unbound txt box, I tried the following two Control Sources:

=[ABegBal]+[SubRpt_AInc.Report.TxtIncomeSum]-
[SubRpt_AExpenses.Report.TxtExpenseSum]

AND
=Sum([ABegBal])+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]

That's more information than I can deal with in a newsgroup
question so I'm not sure what all your problems might be.
The one thing that jumps out at me is that you are being
prompted for some values.

If you are prompted for something in a report, it means that
you used a name that Access can not find in the report.
Your job at that point is to take note of the prompt string
and find where it is used in the report. Then figure out
what name name should have used.

In this case, it appears that you are using two different
names (SubRpt_AInc and SubRpt_ArvestIncome) for a subreport.
Maybe I am just misunderstanding your description and the
prompt for SubRpt_AInc is caused for some other reason.
OTOH, maybe you are confusing the name of the subreport
control and the name of the report object it is displaying.

I think you also said you were being prompted for txtRunAmt.
If so, that would mean that the running sum text box that
you added to the detail section has some other name.
Perhaps you created the text box and forgot to change its
name from the Access made up name to txtRunAmt?

 

[znibk]

Marshall,

I really appreciate a reply on Labor Day and I really hope you can help me.

Just before I posted, I decided to simplify the Names; so it was possible
some of them were mixed in my post. But, I have everything correct in the
main report and sub reports. Everything adds up perfectly; just waiting for
the ending balance.

The other parts of the post were my descriptions of what I'd tried from
suggestions of Duane, and when I tried to modify the ones you used in
Margaret's post. All I should have put was what I had and what I needed (the
2nd paragraph)

When I run the report, everything is correct and lined up perfectly. I just
do not have an ending balance. Here is exactly what I have, from the
development of the subreports to the main report.

Name: SubRpt_AInc
Record Source: SumQry_ArvestIncome
SubRpt_AInc Report Footer:
Name: SubRpt_AInc.TxtIncomeSum
Control Source: =Sum([ATotInc])Name: SubRpt_AExpenses
Record Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_AExpenses Report Footer:
Name: SubRpt_AExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStmt
Reccord Source: Qry_Arvest

Detail Section of Rpt_FinancialStmt
1st Field is:
Name: rpt_AFinancialStmt.Report.TxtBegBalTot
Control Source: ABegBal

2nd Field is first SubReport:
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc
Link Child Field: MonthYear
Link Master Field: MonthYear

3rd Field is 2nd SubReport:
Name: SubRpt_AExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_AExpenses
Link Child Field: MonthYear
Link Master Field: MonthYear

I hope this simplifies things for you, tells you what I do have, and lets
you know that I do not have an Ending Balance. I want to say again that each
field of Rpt_FinancialStmt is correct, and lines up perfectly.

Again Marshall, thank you for replying on Labor Day, I hope you can help me,
and

Christ's blessings,
k

znibk wrote

Your answer to Margaret on 10/17/04 was so well explained, I hope you can
help me out. I’ve been trying for over three weeks to find the solution to
this problem: the Ending Balance of a report with two subreports.

I have the Beginning Balance and the two subreports in the Detail Section of
the Main Report: rpt_AFinancialStmt. I know that I have to add the beginning
balance, the total income together and subtract the total expenses to obtain
the ending balance.

I put an unbound text box in the Detail Section below the SubRpt_AInc in
which I put [ABegBal]+[ATotInc]. I received a post from Duane stating I
needed to have the name of the report and “txtIncomeSum†or “txtExpenseSumâ€
as the Control Source in an unbound text box. I worked with him but have no
end result and I have not heard from him in several days.

I was reading other posts and found the one mentioned above from Margaret,
Calucating Rpt/SubRpt, 10/17/04 and decided I’d post my question with your
name and hopefully you would see it and be able to help me reach achieve an
Ending Balance.

Not fully understanding your post, “If the subreport is in the main report's
detail section, then you need to calculate the total from all instances of
the subreport. This is done by adding a text box named
txtRunAmt to the detail section. Set its control source
expression to:

=IIf(QRYsubrptInvoice.REPORT!HasData,QRYsubrptInvoice.REPORT!idamt,0) and
set its RunningSum property to Over All.

Then the grand total in the main report's footer would use
the expression:
=Sum(amt) + txtRunAmt “


Since I’d tried numerous combinations, in the Detail Section with no
results, I decided to try and interpret what you’d written. I put an unbound
txt box with the control source of
“=IIf(SubRpt_AInc.Report!HasData,SubRpt_AInc.Report!idamt,0) and set the
running sum to all over. And try an unbound txt box in the report footer with
“=Sum(amt) + txtRunAmtâ€

After all, what I’d been through, I did not have anything to loose.
Unfortunately for me, it did not work. When I ran the report, it asked for
SubRpt_AInc. And =Sum(Amt) and txtRunAmt.

Marshall, I really hope you can help me as I have the other half of the
financial statement to create. I never dreamed it would be such an ordeal to
achieve an Ending Balance. I shall be humbled again and truly thankful if you
can help me. k

And, not knowing exactly what you want when trying to help someone, I hope
the following will help you more fully understand what I have. Incidentally,
the beginning balance, total income, and total expenses, are all correct and
line up perfectly as a Financial Report sould.
Now, how do I get the ending balance to show and be accurate with the
following information:

I have a main report, rpt_FinancialStatement. I have the Beginning Balance
and two subreports, SubReport_Income and SubReport_Expenses in the Detail
Section of the main report.

I’m beginning with descriptions of my subreports and will try to put them
in, a manner which makes sense

Name: SubRpt_ArvestIncome
Control Source: SumQry_ArvestIncome
SubRpt_ArvestIncome Report Footer:
Name: SubRpt_ArvestIncome.TxtIncomeSum
Control Source: =Sum([ATotInc])

Name: SubRpt_ArvestClientAndNonClientExpenses
Control Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_ArvestExpenses Report Footer:
Name: SubRpt_ArvestClientAndNonClientExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStatement
Control Source: Qry_Arvest

Detail Section of Rpt_FinancialStatement:
1st Field is:
Name: rpt_FinancialStatement.Report.TxtBegBalSum
Control Source: ABegBal

1st SubRpt is:
Name: SubRpt_ArvestIncome.Report.TxtIncomeSum
Source Object: Report.SubRpt_ArvestIncome
Link Child Field MonthYear
Link Master Field: MonthYear

2nd SubReport is:
Name: SubRpt_ArvestClientAndNonClientExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_ArvestClientAndNonClientExpenses
Link Child Field MonthYear
Link Master Field: MonthYear

Rpt_FinancialStatement Footer
In an unbound txt box, I tried the following two Control Sources:

=[ABegBal]+[SubRpt_AInc.Report.TxtIncomeSum]-
[SubRpt_AExpenses.Report.TxtExpenseSum]

AND
=Sum([ABegBal])+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]

That's more information than I can deal with in a newsgroup
question so I'm not sure what all your problems might be.
The one thing that jumps out at me is that you are being
prompted for some values.

If you are prompted for something in a report, it means that
you used a name that Access can not find in the report.
Your job at that point is to take note of the prompt string
and find where it is used in the report. Then figure out
what name name should have used.

In this case, it appears that you are using two different
names (SubRpt_AInc and SubRpt_ArvestIncome) for a subreport.
Maybe I am just misunderstanding your description and the
prompt for SubRpt_AInc is caused for some other reason.
OTOH, maybe you are confusing the name of the subreport
control and the name of the report object it is displaying.

I think you also said you were being prompted for txtRunAmt.
If so, that would mean that the running sum text box that
you added to the detail section has some other name.
Perhaps you created the text box and forgot to change its
name from the Access made up name to txtRunAmt?

 

[Marshall Barton]

Just before I posted, I decided to simplify the Names; so it was possible
some of them were mixed in my post. But, I have everything correct in the
main report and sub reports. Everything adds up perfectly; just waiting for
the ending balance.

The other parts of the post were my descriptions of what I'd tried from
suggestions of Duane, and when I tried to modify the ones you used in
Margaret's post. All I should have put was what I had and what I needed (the
2nd paragraph)

When I run the report, everything is correct and lined up perfectly. I just
do not have an ending balance. Here is exactly what I have, from the
development of the subreports to the main report.

Name: SubRpt_AInc
Record Source: SumQry_ArvestIncome
SubRpt_AInc Report Footer:
Name: SubRpt_AInc.TxtIncomeSum
Control Source: =Sum([ATotInc])Name: SubRpt_AExpenses
Record Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_AExpenses Report Footer:
Name: SubRpt_AExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStmt
Reccord Source: Qry_Arvest

Detail Section of Rpt_FinancialStmt
1st Field is:
Name: rpt_AFinancialStmt.Report.TxtBegBalTot
Control Source: ABegBal

2nd Field is first SubReport:
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc
Link Child Field: MonthYear
Link Master Field: MonthYear

3rd Field is 2nd SubReport:
Name: SubRpt_AExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_AExpenses
Link Child Field: MonthYear
Link Master Field: MonthYear

I think you need to add a text box (named txtRunInc) to the
detail section. Set its control source expression to:
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
and set its RunningSum property to Over All.

Add another text box (named txtRunExp) to the detail
section. Set its control source expression to:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
and set its RunningSum property to Over All.

Then the ending balance text box in the Report Footer
section can use an expression like:
=Sum(TxtBegBalTot) + txtRunInc - txtRunExp

That may not be exactly what you want because I don't
understand why you have a beginning balance in the detail
section and want a grand total of all the beginning
balances.

Maybe the report's record source query only permits one
detail record?? If that's the case, then using the Sum
function and setting the RunningSum property on the two text
boxes is redundant.

 

[znibk]

Marsh,

I have the Beginning Balance in the Detail Section because I thought that
was where I was supposed to put it. I want a total of the beginning balance
plus the total income minus the expenses. That should provide me with an
ending balance. I just don't know how to do it in Access.

Hope your instructions work for what is described above. k

Just before I posted, I decided to simplify the Names; so it was possible
some of them were mixed in my post. But, I have everything correct in the
main report and sub reports. Everything adds up perfectly; just waiting for
the ending balance.

The other parts of the post were my descriptions of what I'd tried from
suggestions of Duane, and when I tried to modify the ones you used in
Margaret's post. All I should have put was what I had and what I needed (the
2nd paragraph)

I have the Beginning Balance and two subreports in the Detail Section of
a Main Report: rpt_AFinancialStmt. I know that I have to add the beginning
balance, the total income together and subtract the total expenses to obtain
the ending balance. (I then could have added "the efforts I've tried have been hopeless." rather than going into so much detail.

When I run the report, everything is correct and lined up perfectly. I just
do not have an ending balance. Here is exactly what I have, from the
development of the subreports to the main report.

Name: SubRpt_AInc
Record Source: SumQry_ArvestIncome
SubRpt_AInc Report Footer:
Name: SubRpt_AInc.TxtIncomeSum
Control Source: =Sum([ATotInc])Name: SubRpt_AExpenses
Record Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_AExpenses Report Footer:
Name: SubRpt_AExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStmt
Reccord Source: Qry_Arvest

Detail Section of Rpt_FinancialStmt
1st Field is:
Name: rpt_AFinancialStmt.Report.TxtBegBalTot
Control Source: ABegBal

2nd Field is first SubReport:
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc
Link Child Field: MonthYear
Link Master Field: MonthYear

3rd Field is 2nd SubReport:
Name: SubRpt_AExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_AExpenses
Link Child Field: MonthYear
Link Master Field: MonthYear

I think you need to add a text box (named txtRunInc) to the
detail section. Set its control source expression to:
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
and set its RunningSum property to Over All.

Add another text box (named txtRunExp) to the detail
section. Set its control source expression to:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
and set its RunningSum property to Over All.

Then the ending balance text box in the Report Footer
section can use an expression like:
=Sum(TxtBegBalTot) + txtRunInc - txtRunExp

That may not be exactly what you want because I don't
understand why you have a beginning balance in the detail
section and want a grand total of all the beginning
balances.

Maybe the report's record source query only permits one
detail record?? If that's the case, then using the Sum
function and setting the RunningSum property on the two text
boxes is redundant.

 

[znibk]

Marshall,

I have only the one detail section in the main report. Each of the sub
reports have one detail section.

I made the two uncontrolled txt boxes and copied and pasted the IIF
statements you had. I opened the report. The first thing I got was a
parameter asking me for SubRpt_AExpense. I knew that meant research, but
clicked OK and opened the report anyway. I got the #Name? error for both the
txtbox TxtRunInc and TxtRunExp.

I have looked everywhere I can think of and have yet to find my error in
naming which would cause the parameter question. And, I expect the name error
results from that, although I would think txtRunInc should have returned a
sum.

k

You already said all that. What I don't know is if the
report always has just one detail record or if there can be
more than one detail.
--
Marsh
MVP [MS Access]

I have the Beginning Balance in the Detail Section because I thought that
was where I was supposed to put it. I want a total of the beginning balance
plus the total income minus the expenses. That should provide me with an
ending balance. I just don't know how to do it in Access.

Hope your instructions work for what is described above. k

znibk wrote:
Just before I posted, I decided to simplify the Names; so it was possible
some of them were mixed in my post. But, I have everything correct in the
main report and sub reports. Everything adds up perfectly; just waiting for
the ending balance.

The other parts of the post were my descriptions of what I'd tried from
suggestions of Duane, and when I tried to modify the ones you used in
Margaret's post. All I should have put was what I had and what I needed (the
2nd paragraph)

I have the Beginning Balance and two subreports in the Detail Section of
a Main Report: rpt_AFinancialStmt. I know that I have to add the beginning
balance, the total income together and subtract the total expenses to obtain
the ending balance. (I then could have added "the efforts I've tried have been hopeless." rather than going into so much detail.

When I run the report, everything is correct and lined up perfectly. I just
do not have an ending balance. Here is exactly what I have, from the
development of the subreports to the main report.

Name: SubRpt_AInc
Record Source: SumQry_ArvestIncome
SubRpt_AInc Report Footer:
Name: SubRpt_AInc.TxtIncomeSum
Control Source: =Sum([ATotInc])

Name: SubRpt_AExpenses
Record Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_AExpenses Report Footer:
Name: SubRpt_AExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStmt
Reccord Source: Qry_Arvest

Detail Section of Rpt_FinancialStmt
1st Field is:
Name: rpt_AFinancialStmt.Report.TxtBegBalTot
Control Source: ABegBal

2nd Field is first SubReport:
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc
Link Child Field: MonthYear
Link Master Field: MonthYear

3rd Field is 2nd SubReport:
Name: SubRpt_AExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_AExpenses
Link Child Field: MonthYear
Link Master Field: MonthYear


I think you need to add a text box (named txtRunInc) to the
detail section. Set its control source expression to:
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
and set its RunningSum property to Over All.

Add another text box (named txtRunExp) to the detail
section. Set its control source expression to:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
and set its RunningSum property to Over All.

Then the ending balance text box in the Report Footer
section can use an expression like:
=Sum(TxtBegBalTot) + txtRunInc - txtRunExp

That may not be exactly what you want because I don't
understand why you have a beginning balance in the detail
section and want a grand total of all the beginning
balances.

Maybe the report's record source query only permits one
detail record?? If that's the case, then using the Sum
function and setting the RunningSum property on the two text
boxes is redundant.

 

[Marshall Barton]

I guess I am having trouble making myself clear. Reports
only have one detail section, what I need to know is how
many records the main report's record source query returns
to the report. Each record will be displayed in its own
instance of the detail section. The point of my question is
that it is redundant to sum a single detail. The Sum
function in the report footer will work ok, but a grand
total in the report footer may not be what you want if there
can be multiple records.

I can't see your report so I don't know what names you have
used incorrectly in the report. A couple of things you
should check first is that the names in the txtRun...
unbound text box expressions. The first name is the name of
the subreport CONTROL on the main report. This may be
different from the name of the report object displayed in
the subreport control. You can see the subreport control
name by clicking once in the subreport. This should select
the box around the report used as a subreport. Look at the
Name property of the control (the name of the report object
is in the SourceObject property, which we don't care about).
The other name is the name of the total text box in the
subreport's report footer section.

The name in the Sum function must be the name of the FIELD
in the report's record source query, which might or might
not be the same as a text box name. I think I made a
mistake when I said to use =Sum(TxtBegBalTot) + ...
It should be =Sum(ATotInc) + ...
--
Marsh
MVP [MS Access]

I have only the one detail section in the main report. Each of the sub
reports have one detail section.

I made the two uncontrolled txt boxes and copied and pasted the IIF
statements you had. I opened the report. The first thing I got was a
parameter asking me for SubRpt_AExpense. I knew that meant research, but
clicked OK and opened the report anyway. I got the #Name? error for both the
txtbox TxtRunInc and TxtRunExp.

I have looked everywhere I can think of and have yet to find my error in
naming which would cause the parameter question. And, I expect the name error
results from that, although I would think txtRunInc should have returned a
sum.

You already said all that. What I don't know is if the
report always has just one detail record or if there can be
more than one detail.

I have the Beginning Balance in the Detail Section because I thought that
was where I was supposed to put it. I want a total of the beginning balance
plus the total income minus the expenses. That should provide me with an
ending balance. I just don't know how to do it in Access.

Hope your instructions work for what is described above. k

:

znibk wrote:
Just before I posted, I decided to simplify the Names; so it was possible
some of them were mixed in my post. But, I have everything correct in the
main report and sub reports. Everything adds up perfectly; just waiting for
the ending balance.

The other parts of the post were my descriptions of what I'd tried from
suggestions of Duane, and when I tried to modify the ones you used in
Margaret's post. All I should have put was what I had and what I needed (the
2nd paragraph)

I have the Beginning Balance and two subreports in the Detail Section of
a Main Report: rpt_AFinancialStmt. I know that I have to add the beginning
balance, the total income together and subtract the total expenses to obtain
the ending balance. (I then could have added "the efforts I've tried have been hopeless." rather than going into so much detail.

When I run the report, everything is correct and lined up perfectly. I just
do not have an ending balance. Here is exactly what I have, from the
development of the subreports to the main report.

Name: SubRpt_AInc
Record Source: SumQry_ArvestIncome
SubRpt_AInc Report Footer:
Name: SubRpt_AInc.TxtIncomeSum
Control Source: =Sum([ATotInc])

Name: SubRpt_AExpenses
Record Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_AExpenses Report Footer:
Name: SubRpt_AExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStmt
Reccord Source: Qry_Arvest

Detail Section of Rpt_FinancialStmt
1st Field is:
Name: rpt_AFinancialStmt.Report.TxtBegBalTot
Control Source: ABegBal

2nd Field is first SubReport:
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc
Link Child Field: MonthYear
Link Master Field: MonthYear

3rd Field is 2nd SubReport:
Name: SubRpt_AExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_AExpenses
Link Child Field: MonthYear
Link Master Field: MonthYear


I think you need to add a text box (named txtRunInc) to the
detail section. Set its control source expression to:
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
and set its RunningSum property to Over All.

Add another text box (named txtRunExp) to the detail
section. Set its control source expression to:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
and set its RunningSum property to Over All.

Then the ending balance text box in the Report Footer
section can use an expression like:
=Sum(TxtBegBalTot) + txtRunInc - txtRunExp

That may not be exactly what you want because I don't
understand why you have a beginning balance in the detail
section and want a grand total of all the beginning
balances.

Maybe the report's record source query only permits one
detail record?? If that's the case, then using the Sum
function and setting the RunningSum property on the two text
boxes is redundant.

 

[znibk]

Marshall,

When will I ever learn? I spent over a hour posting the details of my
subreports so that you would know the control sources of each one. While I
re-write that in word to copy to this, I will put the Control Source I used
to try and compute the Ending Balance. I think it answers you question about
what the name of my subreports' footers control sources.
=[rpt_AFinancialStmt.Report.TxtBegBalTot]+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]

I received a blank space.

Also, I still receive the name error in the two uncontrolled text boxes, I
put the IIF statements in in the Detail Section. Am I supposed to be doing
something with them?

Thanks, Good Luck, and Christ's blessings,
k

I guess I am having trouble making myself clear. Reports
only have one detail section, what I need to know is how
many records the main report's record source query returns
to the report. Each record will be displayed in its own
instance of the detail section. The point of my question is
that it is redundant to sum a single detail. The Sum
function in the report footer will work ok, but a grand
total in the report footer may not be what you want if there
can be multiple records.

I can't see your report so I don't know what names you have
used incorrectly in the report. A couple of things you
should check first is that the names in the txtRun...
unbound text box expressions. The first name is the name of
the subreport CONTROL on the main report. This may be
different from the name of the report object displayed in
the subreport control. You can see the subreport control
name by clicking once in the subreport. This should select
the box around the report used as a subreport. Look at the
Name property of the control (the name of the report object
is in the SourceObject property, which we don't care about).
The other name is the name of the total text box in the
subreport's report footer section.

The name in the Sum function must be the name of the FIELD
in the report's record source query, which might or might
not be the same as a text box name. I think I made a
mistake when I said to use =Sum(TxtBegBalTot) + ...
It should be =Sum(ATotInc) + ...
--
Marsh
MVP [MS Access]

I have only the one detail section in the main report. Each of the sub
reports have one detail section.

I made the two uncontrolled txt boxes and copied and pasted the IIF
statements you had. I opened the report. The first thing I got was a
parameter asking me for SubRpt_AExpense. I knew that meant research, but
clicked OK and opened the report anyway. I got the #Name? error for both the
txtbox TxtRunInc and TxtRunExp.

I have looked everywhere I can think of and have yet to find my error in
naming which would cause the parameter question. And, I expect the name error
results from that, although I would think txtRunInc should have returned a
sum.

You already said all that. What I don't know is if the
report always has just one detail record or if there can be
more than one detail.


znibk wrote:
I have the Beginning Balance in the Detail Section because I thought that
was where I was supposed to put it. I want a total of the beginning balance
plus the total income minus the expenses. That should provide me with an
ending balance. I just don't know how to do it in Access.

Hope your instructions work for what is described above. k

:

znibk wrote:
Just before I posted, I decided to simplify the Names; so it was possible
some of them were mixed in my post. But, I have everything correct in the
main report and sub reports. Everything adds up perfectly; just waiting for
the ending balance.

The other parts of the post were my descriptions of what I'd tried from
suggestions of Duane, and when I tried to modify the ones you used in
Margaret's post. All I should have put was what I had and what I needed (the
2nd paragraph)

I have the Beginning Balance and two subreports in the Detail Section of
a Main Report: rpt_AFinancialStmt. I know that I have to add the beginning
balance, the total income together and subtract the total expenses to obtain
the ending balance. (I then could have added "the efforts I've tried have been hopeless." rather than going into so much detail.

When I run the report, everything is correct and lined up perfectly. I just
do not have an ending balance. Here is exactly what I have, from the
development of the subreports to the main report.

Name: SubRpt_AInc
Record Source: SumQry_ArvestIncome
SubRpt_AInc Report Footer:
Name: SubRpt_AInc.TxtIncomeSum
Control Source: =Sum([ATotInc])

Name: SubRpt_AExpenses
Record Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_AExpenses Report Footer:
Name: SubRpt_AExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStmt
Reccord Source: Qry_Arvest

Detail Section of Rpt_FinancialStmt
1st Field is:
Name: rpt_AFinancialStmt.Report.TxtBegBalTot
Control Source: ABegBal

2nd Field is first SubReport:
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc
Link Child Field: MonthYear
Link Master Field: MonthYear

3rd Field is 2nd SubReport:
Name: SubRpt_AExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_AExpenses
Link Child Field: MonthYear
Link Master Field: MonthYear


I think you need to add a text box (named txtRunInc) to the
detail section. Set its control source expression to:
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
and set its RunningSum property to Over All.

Add another text box (named txtRunExp) to the detail
section. Set its control source expression to:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
and set its RunningSum property to Over All.

Then the ending balance text box in the Report Footer
section can use an expression like:
=Sum(TxtBegBalTot) + txtRunInc - txtRunExp

That may not be exactly what you want because I don't
understand why you have a beginning balance in the detail
section and want a grand total of all the beginning
balances.

Maybe the report's record source query only permits one
detail record?? If that's the case, then using the Sum
function and setting the RunningSum property on the two text
boxes is redundant.

 

[Marshall Barton]

When will I ever learn? I spent over a hour posting the details of my
subreports so that you would know the control sources of each one. While I
re-write that in word to copy to this, I will put the Control Source I used
to try and compute the Ending Balance. I think it answers you question about
what the name of my subreports' footers control sources.
=[rpt_AFinancialStmt.Report.TxtBegBalTot]+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]

That expression has invalid syntax. The square brackets
enclose too many items. Try this instead:

=[rpt_AFinancialStmt].Report.[TxtBegBalTot]
+ [SubRpt_AInc].Report.[TxtIncomeSum]
- [SubRpt_AExpenses].Report.[TxtExpenseSum]

Also, I still receive the name error in the two uncontrolled text boxes, I
put the IIF statements in in the Detail Section. Am I supposed to be doing
something with them?

Please be more specific. What did you put in the detail
section, the unbound text boxes? What, exactly (use
Copy/Paste) did you put int their control source property?
--
Marsh
MVP [MS Access]

I guess I am having trouble making myself clear. Reports
only have one detail section, what I need to know is how
many records the main report's record source query returns
to the report. Each record will be displayed in its own
instance of the detail section. The point of my question is
that it is redundant to sum a single detail. The Sum
function in the report footer will work ok, but a grand
total in the report footer may not be what you want if there
can be multiple records.

I can't see your report so I don't know what names you have
used incorrectly in the report. A couple of things you
should check first is that the names in the txtRun...
unbound text box expressions. The first name is the name of
the subreport CONTROL on the main report. This may be
different from the name of the report object displayed in
the subreport control. You can see the subreport control
name by clicking once in the subreport. This should select
the box around the report used as a subreport. Look at the
Name property of the control (the name of the report object
is in the SourceObject property, which we don't care about).
The other name is the name of the total text box in the
subreport's report footer section.

The name in the Sum function must be the name of the FIELD
in the report's record source query, which might or might
not be the same as a text box name. I think I made a
mistake when I said to use =Sum(TxtBegBalTot) + ...
It should be =Sum(ATotInc) + ...
--
Marsh
MVP [MS Access]

I have only the one detail section in the main report. Each of the sub
reports have one detail section.

I made the two uncontrolled txt boxes and copied and pasted the IIF
statements you had. I opened the report. The first thing I got was a
parameter asking me for SubRpt_AExpense. I knew that meant research, but
clicked OK and opened the report anyway. I got the #Name? error for both the
txtbox TxtRunInc and TxtRunExp.

I have looked everywhere I can think of and have yet to find my error in
naming which would cause the parameter question. And, I expect the name error
results from that, although I would think txtRunInc should have returned a
sum.


:
You already said all that. What I don't know is if the
report always has just one detail record or if there can be
more than one detail.


znibk wrote:
I have the Beginning Balance in the Detail Section because I thought that
was where I was supposed to put it. I want a total of the beginning balance
plus the total income minus the expenses. That should provide me with an
ending balance. I just don't know how to do it in Access.

Hope your instructions work for what is described above. k

:

znibk wrote:
Just before I posted, I decided to simplify the Names; so it was possible
some of them were mixed in my post. But, I have everything correct in the
main report and sub reports. Everything adds up perfectly; just waiting for
the ending balance.

The other parts of the post were my descriptions of what I'd tried from
suggestions of Duane, and when I tried to modify the ones you used in
Margaret's post. All I should have put was what I had and what I needed (the
2nd paragraph)

I have the Beginning Balance and two subreports in the Detail Section of
a Main Report: rpt_AFinancialStmt. I know that I have to add the beginning
balance, the total income together and subtract the total expenses to obtain
the ending balance. (I then could have added "the efforts I've tried have been hopeless." rather than going into so much detail.

When I run the report, everything is correct and lined up perfectly. I just
do not have an ending balance. Here is exactly what I have, from the
development of the subreports to the main report.

Name: SubRpt_AInc
Record Source: SumQry_ArvestIncome
SubRpt_AInc Report Footer:
Name: SubRpt_AInc.TxtIncomeSum
Control Source: =Sum([ATotInc])

Name: SubRpt_AExpenses
Record Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_AExpenses Report Footer:
Name: SubRpt_AExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStmt
Reccord Source: Qry_Arvest

Detail Section of Rpt_FinancialStmt
1st Field is:
Name: rpt_AFinancialStmt.Report.TxtBegBalTot
Control Source: ABegBal

2nd Field is first SubReport:
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc
Link Child Field: MonthYear
Link Master Field: MonthYear

3rd Field is 2nd SubReport:
Name: SubRpt_AExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_AExpenses
Link Child Field: MonthYear
Link Master Field: MonthYear


I think you need to add a text box (named txtRunInc) to the
detail section. Set its control source expression to:
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
and set its RunningSum property to Over All.

Add another text box (named txtRunExp) to the detail
section. Set its control source expression to:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
and set its RunningSum property to Over All.

Then the ending balance text box in the Report Footer
section can use an expression like:
=Sum(TxtBegBalTot) + txtRunInc - txtRunExp

That may not be exactly what you want because I don't
understand why you have a beginning balance in the detail
section and want a grand total of all the beginning
balances.

Maybe the report's record source query only permits one
detail record?? If that's the case, then using the Sum
function and setting the RunningSum property on the two text
boxes is redundant.

 

[znibk]

Good morning Marshall,

I hope this tells you what I am doing wrong. And, I can't say enough, thank
you. k

These are my subreports as they stand alone

First Subreport:
SubRpt_AInc
Detail Section:
Name: IncomeType
Control Source: IncType
Name: ATotalIncome
Control Source: ATotInc

In the Report Footer, I have:
Name: SubRpt_AInc.Report.TxtIncomeSum
Control Source: =Sum([ATotInc])
*I tried using =Sum([ATotalIncome]) but drew a blank. I get the correct
income total with =Sum([ATotInc])

Second Subreport:
SubRpt_AExpenses
Whoes Header (Tells whether expense is Client or NonClient)
Name: WhoesExpense
Control Source: Whoes
Name: ATotalExpense
Control Source: ATotExp
Detail Section:
Name: TypeExpense
Control Source: Description
Name: ATotExpSum
Control Source: =Sum([ATotExp])
Whoes Footer:
Name: WhoesExpenseIsIt
Control Source: ="Total " & [WhoesExpense] & " :"
Name: ATotExpSum
Control Source: =Sum([ATotExp])
SubRpt_AExpenses Footer:
Name: SubRpt_AExpenses.TxtExpenseSum
Control Source: =Sum([ATotExp])
As they appear in the Detail Section of the Main Report:
Rpt_AFinancialStmt
Name: rpt_AFinancialStmt.Report.TxtBegBalTot
Control Source: ABegBal
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc
Master/Child Link: MonthYear
Name: SubRpt_AExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_AExpenses
Master/Child Link: MonthYear

Two unbound text boxes with the IIF statements
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
*Each return #Name? Error

Rpt_AFinancialStmt Footer
=[rpt_AFinancialStmt.Report.TxtBegBalTot]+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]
*Returns blank space

Christ's blessings and good luck,
k

When will I ever learn? I spent over a hour posting the details of my
subreports so that you would know the control sources of each one. While I
re-write that in word to copy to this, I will put the Control Source I used
to try and compute the Ending Balance. I think it answers you question about
what the name of my subreports' footers control sources.
=[rpt_AFinancialStmt.Report.TxtBegBalTot]+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]

That expression has invalid syntax. The square brackets
enclose too many items. Try this instead:

=[rpt_AFinancialStmt].Report.[TxtBegBalTot]
+ [SubRpt_AInc].Report.[TxtIncomeSum]
- [SubRpt_AExpenses].Report.[TxtExpenseSum]

Also, I still receive the name error in the two uncontrolled text boxes, I
put the IIF statements in in the Detail Section. Am I supposed to be doing
something with them?

Please be more specific. What did you put in the detail
section, the unbound text boxes? What, exactly (use
Copy/Paste) did you put int their control source property?
--
Marsh
MVP [MS Access]

I guess I am having trouble making myself clear. Reports
only have one detail section, what I need to know is how
many records the main report's record source query returns
to the report. Each record will be displayed in its own
instance of the detail section. The point of my question is
that it is redundant to sum a single detail. The Sum
function in the report footer will work ok, but a grand
total in the report footer may not be what you want if there
can be multiple records.

I can't see your report so I don't know what names you have
used incorrectly in the report. A couple of things you
should check first is that the names in the txtRun...
unbound text box expressions. The first name is the name of
the subreport CONTROL on the main report. This may be
different from the name of the report object displayed in
the subreport control. You can see the subreport control
name by clicking once in the subreport. This should select
the box around the report used as a subreport. Look at the
Name property of the control (the name of the report object
is in the SourceObject property, which we don't care about).
The other name is the name of the total text box in the
subreport's report footer section.

The name in the Sum function must be the name of the FIELD
in the report's record source query, which might or might
not be the same as a text box name. I think I made a
mistake when I said to use =Sum(TxtBegBalTot) + ...
It should be =Sum(ATotInc) + ...
--
Marsh
MVP [MS Access]


znibk wrote:
I have only the one detail section in the main report. Each of the sub
reports have one detail section.

I made the two uncontrolled txt boxes and copied and pasted the IIF
statements you had. I opened the report. The first thing I got was a
parameter asking me for SubRpt_AExpense. I knew that meant research, but
clicked OK and opened the report anyway. I got the #Name? error for both the
txtbox TxtRunInc and TxtRunExp.

I have looked everywhere I can think of and have yet to find my error in
naming which would cause the parameter question. And, I expect the name error
results from that, although I would think txtRunInc should have returned a
sum.


:
You already said all that. What I don't know is if the
report always has just one detail record or if there can be
more than one detail.


znibk wrote:
I have the Beginning Balance in the Detail Section because I thought that
was where I was supposed to put it. I want a total of the beginning balance
plus the total income minus the expenses. That should provide me with an
ending balance. I just don't know how to do it in Access.

Hope your instructions work for what is described above. k

:

znibk wrote:
Just before I posted, I decided to simplify the Names; so it was possible
some of them were mixed in my post. But, I have everything correct in the
main report and sub reports. Everything adds up perfectly; just waiting for
the ending balance.

The other parts of the post were my descriptions of what I'd tried from
suggestions of Duane, and when I tried to modify the ones you used in
Margaret's post. All I should have put was what I had and what I needed (the
2nd paragraph)

I have the Beginning Balance and two subreports in the Detail Section of
a Main Report: rpt_AFinancialStmt. I know that I have to add the beginning
balance, the total income together and subtract the total expenses to obtain
the ending balance. (I then could have added "the efforts I've tried have been hopeless." rather than going into so much detail.

When I run the report, everything is correct and lined up perfectly. I just
do not have an ending balance. Here is exactly what I have, from the
development of the subreports to the main report.

Name: SubRpt_AInc
Record Source: SumQry_ArvestIncome
SubRpt_AInc Report Footer:
Name: SubRpt_AInc.TxtIncomeSum
Control Source: =Sum([ATotInc])

Name: SubRpt_AExpenses
Record Source: SumQry_ArvestClientAndNonClientExpenses
SubRpt_AExpenses Report Footer:
Name: SubRpt_AExpenses.TxtExpenseSum)
Control Source: =Sum([ATotExp])

Main Report:
Name: Rpt_FinancialStmt
Reccord Source: Qry_Arvest

Detail Section of Rpt_FinancialStmt
1st Field is:
Name: rpt_AFinancialStmt.Report.TxtBegBalTot
Control Source: ABegBal

2nd Field is first SubReport:
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc
Link Child Field: MonthYear
Link Master Field: MonthYear

3rd Field is 2nd SubReport:
Name: SubRpt_AExpenses.Report.TxtExpenseSum
Source Object: Report.SubRpt_AExpenses
Link Child Field: MonthYear
Link Master Field: MonthYear


I think you need to add a text box (named txtRunInc) to the
detail section. Set its control source expression to:
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
and set its RunningSum property to Over All.

Add another text box (named txtRunExp) to the detail
section. Set its control source expression to:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
and set its RunningSum property to Over All.

Then the ending balance text box in the Report Footer
section can use an expression like:
=Sum(TxtBegBalTot) + txtRunInc - txtRunExp

That may not be exactly what you want because I don't
understand why you have a beginning balance in the detail
section and want a grand total of all the beginning
balances.

Maybe the report's record source query only permits one
detail record?? If that's the case, then using the Sum
function and setting the RunningSum property on the two text
boxes is redundant.

 

[Marshall Barton]

I tried to make it a little easier to read subject to
newsreader line wrapping no making a mess of it. Please
verify that I didn't change anything other than how it's
laid out.

See below with comments imedded:
--
Marsh
MVP [MS Access]

I hope this tells you what I am doing wrong. And, I can't say enough, thank
you. k

These are my subreports as they stand alone

Subreport: SubRpt_AInc
Detail Section:
Text box Name: IncomeType
ControlSource: IncType
Text box Name: ATotalIncome
ControlSource: ATotInc
Report Footer Section:
Text box Name: TxtIncomeSum
ControlSource: =Sum([ATotInc])

Subreport: SubRpt_AExpenses
Whoes Group Header Whoes (Client or NonClient)
Text box Name: WhoesExpense
ControlSource: Whoes
Text box Name: ATotalExpense
ControlSource: ATotExp
Detail Section:
Text box Name: TypeExpense
ControlSource: Description
// This text box makes no sense to me in the detail section
// Maybe it's just a typing dup of the one in the group
// footer section and is not really here??
Text box Name: ATotExpSum
ControlSource: =Sum([ATotExp])
//
Whoes Group Footer:
Text box Name: WhoesExpenseIsIt
ControlSource: ="Total " &
[WhoesExpense] & " :"
Text box Name: ATotExpSum
ControlSource: =Sum([ATotExp])
Report Footer Section:
Text box Name: TxtExpenseSum
ControlSource: =Sum([ATotExp])

Main report: Rpt_AFinancialStmt
Detail Section:
Text box Name: TxtBegBalTot
ControlSource: ABegBal
Subreport Name: SubRpt_AInc
SourceObject: Report.SubRpt_AInc
Master/Child Link: MonthYear
Text box Name: txtRunInc
RunningSum: Over All
ControlSource:
//Unless your names are different from what you posted,
//I don't see how this can cause a #Name error.
//Double check the subreport control's Name and SourceObject
//properties
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
Subreport Name: SubRpt_AExpenses
SourceObject: Report.SubRpt_AExpenses
Master/Child Link: MonthYear
Text box Name: txtRunExp
RunningSum: Over All
ControlSource:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
Report Footer Section:
Text box Name: ???
ControlSource:
//This is all wrong:
=[rpt_AFinancialStmt.Report.TxtBegBalTot]+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]
//Try changing it to:
=TxtBegBalTot + txtRunInc - txtRunExp
//but until we resolve the #Name errors above,
//it won't show a result

Two unbound text boxes with the IIF statements
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
*Each return #Name? Error

Rpt_AFinancialStmt Footer
=[rpt_AFinancialStmt.Report.TxtBegBalTot]+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]
*Returns blank space

"Marshall Barton" wrote: []
I can't see your report so I don't know what names you have
used incorrectly in the report. A couple of things you
should check first is that the names in the txtRun...
unbound text box expressions. The first name is the name of
the subreport CONTROL on the main report. This may be
different from the name of the report object displayed in
the subreport control. You can see the subreport control
name by clicking once in the subreport. This should select
the box around the report used as a subreport. Look at the
Name property of the control (the name of the report object
is in the SourceObject property, which we don't care about).
The other name is the name of the total text box in the
subreport's report footer section.

 

[znibk]

Good evening Marshall,

Thank you. I'm learning about this posting business! Although you may not
think so after reading this post.

I have not gone through everything yet. I wanted to address the Description
Text Boxes and Control Source.
1st issue, I moved it out of the Detail Section into a Description Header.
2nd issue, As I was getting everything ready for you, I noticed the Control
Source for the amount of one check was exactly the same as was in the Report
Footer for the sum of all the expense checks.

That steams from the Record Source for my SubRpt_AExpenses, which is
SumQry_ArvestClientAndNonClientExpenses
It is not correct, I do not think. the reason I think it is not correct is
because of what I said above. Here is the SQL view.

SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description
HAVING (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));

I'm not sure what I was thinking when I did that except that I did not want
to stack [SumOfAClCkAmt] on top of [SumOfANonClCkAmt] (as one or the other is
always going to be null or 0) beside the Control Source Description.

What I'm saying is that I want a line which would be a total of , for
example, NonClient Telephone Expenses 95.00
Petty Cash 45.00

The only way I knew to do it without stacking, was to have a field which
would stand for both types of checks.

I really hope that makes sense and you can tell me a better way.

Perhaps the better way would be to make the Control Source =IIf(Nz
[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])

I am now on a rescue and search misson. Wish me luck and I wish you luck
deciphering, thinking, and solving.

Again, Christ's blessings,
k

I tried to make it a little easier to read subject to
newsreader line wrapping no making a mess of it. Please
verify that I didn't change anything other than how it's
laid out.

See below with comments imedded:
--
Marsh
MVP [MS Access]

I hope this tells you what I am doing wrong. And, I can't say enough, thank
you. k

These are my subreports as they stand alone

Subreport: SubRpt_AInc
Detail Section:
Text box Name: IncomeType
ControlSource: IncType
Text box Name: ATotalIncome
ControlSource: ATotInc
Report Footer Section:
Text box Name: TxtIncomeSum
ControlSource: =Sum([ATotInc])

Subreport: SubRpt_AExpenses
Whoes Group Header Whoes (Client or NonClient)
Text box Name: WhoesExpense
ControlSource: Whoes
Text box Name: ATotalExpense
ControlSource: ATotExp
Detail Section:
Text box Name: TypeExpense
ControlSource: Description
// This text box makes no sense to me in the detail section
// Maybe it's just a typing dup of the one in the group
// footer section and is not really here??
Text box Name: ATotExpSum
ControlSource: =Sum([ATotExp])
//
Whoes Group Footer:
Text box Name: WhoesExpenseIsIt
ControlSource: ="Total " &
[WhoesExpense] & " :"
Text box Name: ATotExpSum
ControlSource: =Sum([ATotExp])
Report Footer Section:
Text box Name: TxtExpenseSum
ControlSource: =Sum([ATotExp])

Main report: Rpt_AFinancialStmt
Detail Section:
Text box Name: TxtBegBalTot
ControlSource: ABegBal
Subreport Name: SubRpt_AInc
SourceObject: Report.SubRpt_AInc
Master/Child Link: MonthYear
Text box Name: txtRunInc
RunningSum: Over All
ControlSource:
//Unless your names are different from what you posted,
//I don't see how this can cause a #Name error.
//Double check the subreport control's Name and SourceObject
//properties
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
Subreport Name: SubRpt_AExpenses
SourceObject: Report.SubRpt_AExpenses
Master/Child Link: MonthYear
Text box Name: txtRunExp
RunningSum: Over All
ControlSource:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
Report Footer Section:
Text box Name: ???
ControlSource:
//This is all wrong:
=[rpt_AFinancialStmt.Report.TxtBegBalTot]+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]
//Try changing it to:
=TxtBegBalTot + txtRunInc - txtRunExp
//but until we resolve the #Name errors above,
//it won't show a result

Two unbound text boxes with the IIF statements
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)
*Each return #Name? Error

Rpt_AFinancialStmt Footer
=[rpt_AFinancialStmt.Report.TxtBegBalTot]+[SubRpt_AInc.Report.TxtIncomeSum]-[SubRpt_AExpenses.Report.TxtExpenseSum]
*Returns blank space

"Marshall Barton" wrote: []
I can't see your report so I don't know what names you have
used incorrectly in the report. A couple of things you
should check first is that the names in the txtRun...
unbound text box expressions. The first name is the name of
the subreport CONTROL on the main report. This may be
different from the name of the report object displayed in
the subreport control. You can see the subreport control
name by clicking once in the subreport. This should select
the box around the report used as a subreport. Look at the
Name property of the control (the name of the report object
is in the SourceObject property, which we don't care about).
The other name is the name of the total text box in the
subreport's report footer section.

 

[Marshall Barton]

Thank you. I'm learning about this posting business! Although you may not
think so after reading this post.

I have not gone through everything yet. I wanted to address the Description
Text Boxes and Control Source.
1st issue, I moved it out of the Detail Section into a Description Header.
2nd issue, As I was getting everything ready for you, I noticed the Control
Source for the amount of one check was exactly the same as was in the Report
Footer for the sum of all the expense checks.

That steams from the Record Source for my SubRpt_AExpenses, which is
SumQry_ArvestClientAndNonClientExpenses
It is not correct, I do not think. the reason I think it is not correct is
because of what I said above. Here is the SQL view.

SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description
HAVING (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));

I'm not sure what I was thinking when I did that except that I did not want
to stack [SumOfAClCkAmt] on top of [SumOfANonClCkAmt] (as one or the other is
always going to be null or 0) beside the Control Source Description.

What I'm saying is that I want a line which would be a total of , for
example, NonClient Telephone Expenses 95.00
Petty Cash 45.00

The only way I knew to do it without stacking, was to have a field which
would stand for both types of checks.

I really hope that makes sense and you can tell me a better way.

Perhaps the better way would be to make the Control Source =IIf(Nz
[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])

I think your =IIf( . . . ) idea might be the easiest.

Note that you should change the query to use WHERE instead
of HAVING. The HAVING clause is for checking if an
aggregated value (e.g. Sum) is acceptable, but it is nowhere
near as efficient as the WHERE clause for ordinary values.

 

[znibk]

Good morning Marshall,

Since the SQL view of the query was created automatically as I used the
design view, and I'm not familiar with the WHERE clause, do I just replace
the word HAVING in the SQL view with WHERE, or is there more to it than that?

Also, my IIF statement is returning 0, so I've something wrong, any ideas?
=IIf(Nz[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])

k

Thank you. I'm learning about this posting business! Although you may not
think so after reading this post.

I have not gone through everything yet. I wanted to address the Description
Text Boxes and Control Source.
1st issue, I moved it out of the Detail Section into a Description Header.
2nd issue, As I was getting everything ready for you, I noticed the Control
Source for the amount of one check was exactly the same as was in the Report
Footer for the sum of all the expense checks.

That steams from the Record Source for my SubRpt_AExpenses, which is
SumQry_ArvestClientAndNonClientExpenses
It is not correct, I do not think. the reason I think it is not correct is
because of what I said above. Here is the SQL view.

SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description
HAVING (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));

I'm not sure what I was thinking when I did that except that I did not want
to stack [SumOfAClCkAmt] on top of [SumOfANonClCkAmt] (as one or the other is
always going to be null or 0) beside the Control Source Description.

What I'm saying is that I want a line which would be a total of , for
example, NonClient Telephone Expenses 95.00
Petty Cash 45.00

The only way I knew to do it without stacking, was to have a field which
would stand for both types of checks.

I really hope that makes sense and you can tell me a better way.

Perhaps the better way would be to make the Control Source =IIf(Nz
[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])

I think your =IIf( . . . ) idea might be the easiest.

Note that you should change the query to use WHERE instead
of HAVING. The HAVING clause is for checking if an
aggregated value (e.g. Sum) is acceptable, but it is nowhere
near as efficient as the WHERE clause for ordinary values.

 

[Marshall Barton]

Not quite, change the word HAVING to the word WHERE in SQL
view and then move the GROUP BY clause down after the WHERE
clause. The converter from the query design view to the
corresponding SQL statement is too dumb to make the
determine when to use the appropriate clause. As I said
before, HAVING will work, but it can be much less efficient
than WHERE. This is because WHERE filters out the undesired
records before doing all the calculations, but HAVING does
all the calculations before deciding that some/many/most of
those calculations were unnecessary.

Your IIf is missing the comparison:

=IIf(Nz[SumOfAClCkAmt],0)=0,[SumOfANonClCkAmt],[SumOfAClCkAmt])
--
Marsh
MVP [MS Access]

Since the SQL view of the query was created automatically as I used the
design view, and I'm not familiar with the WHERE clause, do I just replace
the word HAVING in the SQL view with WHERE, or is there more to it than that?

Also, my IIF statement is returning 0, so I've something wrong, any ideas?
=IIf(Nz[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])

Thank you. I'm learning about this posting business! Although you may not
think so after reading this post.

I have not gone through everything yet. I wanted to address the Description
Text Boxes and Control Source.
1st issue, I moved it out of the Detail Section into a Description Header.
2nd issue, As I was getting everything ready for you, I noticed the Control
Source for the amount of one check was exactly the same as was in the Report
Footer for the sum of all the expense checks.

That steams from the Record Source for my SubRpt_AExpenses, which is
SumQry_ArvestClientAndNonClientExpenses
It is not correct, I do not think. the reason I think it is not correct is
because of what I said above. Here is the SQL view.

SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description
HAVING (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));

I'm not sure what I was thinking when I did that except that I did not want
to stack [SumOfAClCkAmt] on top of [SumOfANonClCkAmt] (as one or the other is
always going to be null or 0) beside the Control Source Description.

What I'm saying is that I want a line which would be a total of , for
example, NonClient Telephone Expenses 95.00
Petty Cash 45.00

The only way I knew to do it without stacking, was to have a field which
would stand for both types of checks.

I really hope that makes sense and you can tell me a better way.

Perhaps the better way would be to make the Control Source =IIf(Nz
[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])

I think your =IIf( . . . ) idea might be the easiest.

Note that you should change the query to use WHERE instead
of HAVING. The HAVING clause is for checking if an
aggregated value (e.g. Sum) is acceptable, but it is nowhere
near as efficient as the WHERE clause for ordinary values.

 

[znibk]

Marshall,

I'm getting closer with my IIF statement. I get the correct amount, however,
I get a 0before the number and no decimal point and two zeros if it is a
whole number, ie
02696; 0259.85 or 96.550
I have the text box formatted to standard with 2 decimals
Here is my IIF statement
=IIf("Nz([SumOfAClCkAmt],0)",[SumOfANonClCkAmt],[SumOfAClCkAmt]) &
IIf("Nz([SumOfANonClCkAmt],0)",[SumOfAClCkAmt],[SumOfANonClCkAmt])

If I remove the " from before and after the Nz statement, I get 00 So I tried

I thought maybe it was
the=IIf(IsNull([SumOfAClCkAmt]),[SumOfANonClCkAmt],[SumOfAClCkAmt]) &
IIf(IsNull([SumOfANonClCkAmt]),[SumOfAClCkAmt],[SumOfANonClCkAmt])

I get the same results as the first IIf statement. ("Where of where has my
little dog gone?") One could substitute the word "brain" and wonder.

Any suggestions?

Thank you for your patience and God bless,
k

Thank you. I'm learning about this posting business! Although you may not
think so after reading this post.

I have not gone through everything yet. I wanted to address the Description
Text Boxes and Control Source.
1st issue, I moved it out of the Detail Section into a Description Header.
2nd issue, As I was getting everything ready for you, I noticed the Control
Source for the amount of one check was exactly the same as was in the Report
Footer for the sum of all the expense checks.

That steams from the Record Source for my SubRpt_AExpenses, which is
SumQry_ArvestClientAndNonClientExpenses
It is not correct, I do not think. the reason I think it is not correct is
because of what I said above. Here is the SQL view.

SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description
HAVING (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));

I'm not sure what I was thinking when I did that except that I did not want
to stack [SumOfAClCkAmt] on top of [SumOfANonClCkAmt] (as one or the other is
always going to be null or 0) beside the Control Source Description.

What I'm saying is that I want a line which would be a total of , for
example, NonClient Telephone Expenses 95.00
Petty Cash 45.00

The only way I knew to do it without stacking, was to have a field which
would stand for both types of checks.

I really hope that makes sense and you can tell me a better way.

Perhaps the better way would be to make the Control Source =IIf(Nz
[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])

I think your =IIf( . . . ) idea might be the easiest.

Note that you should change the query to use WHERE instead
of HAVING. The HAVING clause is for checking if an
aggregated value (e.g. Sum) is acceptable, but it is nowhere
near as efficient as the WHERE clause for ordinary values.

 

[znibk]

Yea, Marshall, you are great. I knew it was probably some simple little thing
in my IIF statement, but you can tell by my other post, that I had to keep
trying while I waited.

Adding the comparison "=0" worked great; everything looks great.

But, I did what you said to the SQL view on the query, changed the word
HAVING to WHERE and moved the GROUP statement below it. I got this message
when I tried to move it back tothe Design View.

"The LEVEL clause includes a reserved word or argument that is misspelled or
missing, or the punctuation is incorrect.

Here is the SQL View:
SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
WHERE (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description

Good luck,
k

P.S. I'm still looking for the error for our unbound text boxes giving the
#name? error.

Not quite, change the word HAVING to the word WHERE in SQL
view and then move the GROUP BY clause down after the WHERE
clause. The converter from the query design view to the
corresponding SQL statement is too dumb to make the
determine when to use the appropriate clause. As I said
before, HAVING will work, but it can be much less efficient
than WHERE. This is because WHERE filters out the undesired
records before doing all the calculations, but HAVING does
all the calculations before deciding that some/many/most of
those calculations were unnecessary.

Your IIf is missing the comparison:

=IIf(Nz[SumOfAClCkAmt],0)=0,[SumOfANonClCkAmt],[SumOfAClCkAmt])
--
Marsh
MVP [MS Access]

Since the SQL view of the query was created automatically as I used the
design view, and I'm not familiar with the WHERE clause, do I just replace
the word HAVING in the SQL view with WHERE, or is there more to it than that?

Also, my IIF statement is returning 0, so I've something wrong, any ideas?
=IIf(Nz[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])

znibk wrote:
Thank you. I'm learning about this posting business! Although you may not
think so after reading this post.

I have not gone through everything yet. I wanted to address the Description
Text Boxes and Control Source.
1st issue, I moved it out of the Detail Section into a Description Header.
2nd issue, As I was getting everything ready for you, I noticed the Control
Source for the amount of one check was exactly the same as was in the Report
Footer for the sum of all the expense checks.

That steams from the Record Source for my SubRpt_AExpenses, which is
SumQry_ArvestClientAndNonClientExpenses
It is not correct, I do not think. the reason I think it is not correct is
because of what I said above. Here is the SQL view.

SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description
HAVING (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));

I'm not sure what I was thinking when I did that except that I did not want
to stack [SumOfAClCkAmt] on top of [SumOfANonClCkAmt] (as one or the other is
always going to be null or 0) beside the Control Source Description.

What I'm saying is that I want a line which would be a total of , for
example, NonClient Telephone Expenses 95.00
Petty Cash 45.00

The only way I knew to do it without stacking, was to have a field which
would stand for both types of checks.

I really hope that makes sense and you can tell me a better way.

Perhaps the better way would be to make the Control Source =IIf(Nz
[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])

I think your =IIf( . . . ) idea might be the easiest.

Note that you should change the query to use WHERE instead
of HAVING. The HAVING clause is for checking if an
aggregated value (e.g. Sum) is acceptable, but it is nowhere
near as efficient as the WHERE clause for ordinary values.

 

[Marshall Barton]

I forgot to tell you to remove the semi colon.

The #Name is because there is a name in the IIf that Access
can not find. I need to see it to make sure the syntax is
correct or if something else is wrong. Because you have
made this mistake before, the first thing you should check
is that any square brackets enclose only a single name. If
that's not it, double check that you are using subreport
***control*** names, which might be different from the
actual name of the report you are using as a subreport.
--
Marsh
MVP [MS Access]

Yea, Marshall, you are great. I knew it was probably some simple little thing
in my IIF statement, but you can tell by my other post, that I had to keep
trying while I waited.

Adding the comparison "=0" worked great; everything looks great.

But, I did what you said to the SQL view on the query, changed the word
HAVING to WHERE and moved the GROUP statement below it. I got this message
when I tried to move it back tothe Design View.

"The LEVEL clause includes a reserved word or argument that is misspelled or
missing, or the punctuation is incorrect.

Here is the SQL View:
SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
WHERE (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description


P.S. I'm still looking for the error for our unbound text boxes giving the
#name? error.

Not quite, change the word HAVING to the word WHERE in SQL
view and then move the GROUP BY clause down after the WHERE
clause. The converter from the query design view to the
corresponding SQL statement is too dumb to make the
determine when to use the appropriate clause. As I said
before, HAVING will work, but it can be much less efficient
than WHERE. This is because WHERE filters out the undesired
records before doing all the calculations, but HAVING does
all the calculations before deciding that some/many/most of
those calculations were unnecessary.

Your IIf is missing the comparison:

=IIf(Nz[SumOfAClCkAmt],0)=0,[SumOfANonClCkAmt],[SumOfAClCkAmt])

Since the SQL view of the query was created automatically as I used the
design view, and I'm not familiar with the WHERE clause, do I just replace
the word HAVING in the SQL view with WHERE, or is there more to it than that?

Also, my IIF statement is returning 0, so I've something wrong, any ideas?
=IIf(Nz[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])


znibk wrote:
Thank you. I'm learning about this posting business! Although you may not
think so after reading this post.

I have not gone through everything yet. I wanted to address the Description
Text Boxes and Control Source.
1st issue, I moved it out of the Detail Section into a Description Header.
2nd issue, As I was getting everything ready for you, I noticed the Control
Source for the amount of one check was exactly the same as was in the Report
Footer for the sum of all the expense checks.

That steams from the Record Source for my SubRpt_AExpenses, which is
SumQry_ArvestClientAndNonClientExpenses
It is not correct, I do not think. the reason I think it is not correct is
because of what I said above. Here is the SQL view.

SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description
HAVING (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));

I'm not sure what I was thinking when I did that except that I did not want
to stack [SumOfAClCkAmt] on top of [SumOfANonClCkAmt] (as one or the other is
always going to be null or 0) beside the Control Source Description.

What I'm saying is that I want a line which would be a total of , for
example, NonClient Telephone Expenses 95.00
Petty Cash 45.00

The only way I knew to do it without stacking, was to have a field which
would stand for both types of checks.

I really hope that makes sense and you can tell me a better way.

Perhaps the better way would be to make the Control Source =IIf(Nz
[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])


:
I think your =IIf( . . . ) idea might be the easiest.

Note that you should change the query to use WHERE instead
of HAVING. The HAVING clause is for checking if an
aggregated value (e.g. Sum) is acceptable, but it is nowhere
near as efficient as the WHERE clause for ordinary values.

 

[znibk]

Marshall,

Removing the semi colon did the trick.

Here is my main report Detail Section:

Name: TxtBegBalTot
Control Source: ABegBal

First Subreport
SubRpt_AInc
Name: TxtIncomeSum
Source Object: Report.SubRpt_AInc

Second Subreport
SubRpt_AExpenses
Name: TxtExpenseSum
Source Object: Report.SubRpt_AExpenses

First IIF text box:
Name: TxtRunInc
Control Source:
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)

Second IIF text box:
Name: TxtRunExp
Control Source:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)

Both are set to Running Sum: Over All

Report Footer:
=[TxtBegBalTot]+[TxtIncomeSum]-[TxtExpenseSum]

All information was copied and pasted.

Marshall, I think I might have found part of the problem??

I was concentrating so hard on these text boxes that I did not notice there
were two other pages that showed up. Each had a different beginning balance
but the income and expenses were the same.

Remember, we talked about the fact that the Subreports were linked to the
main report using the MonthYear.

The frmDates opens the report with the dates txtStart and txtEnd, which
gives us, of course, 8/1/07 ; 8/31/07 When in actual fact, I think we may
need a frmMonthYear (or frmRefDate) where I can enter, ie Aug 2007
I created a frmRefDate with the unbound text box, however, I forced the
query builder to SELECT tblMONTHYEAR.MONTH_YEAR, tblMONTHYEAR.MonthYear FROM
tblMONTHYEAR;

Unfortunately, it did not solve the problem of additional reportswith
different beginning balances.

Hope you can find what's wrong, I've looked at each one so closely, I feel
my eyes are crossed.

Good luck and Christ's blessings,
k

I forgot to tell you to remove the semi colon.

The #Name is because there is a name in the IIf that Access
can not find. I need to see it to make sure the syntax is
correct or if something else is wrong. Because you have
made this mistake before, the first thing you should check
is that any square brackets enclose only a single name. If
that's not it, double check that you are using subreport
***control*** names, which might be different from the
actual name of the report you are using as a subreport.
--
Marsh
MVP [MS Access]

Yea, Marshall, you are great. I knew it was probably some simple little thing
in my IIF statement, but you can tell by my other post, that I had to keep
trying while I waited.

Adding the comparison "=0" worked great; everything looks great.

But, I did what you said to the SQL view on the query, changed the word
HAVING to WHERE and moved the GROUP statement below it. I got this message
when I tried to move it back tothe Design View.

"The LEVEL clause includes a reserved word or argument that is misspelled or
missing, or the punctuation is incorrect.

Here is the SQL View:
SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
WHERE (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description


P.S. I'm still looking for the error for our unbound text boxes giving the
#name? error.

Not quite, change the word HAVING to the word WHERE in SQL
view and then move the GROUP BY clause down after the WHERE
clause. The converter from the query design view to the
corresponding SQL statement is too dumb to make the
determine when to use the appropriate clause. As I said
before, HAVING will work, but it can be much less efficient
than WHERE. This is because WHERE filters out the undesired
records before doing all the calculations, but HAVING does
all the calculations before deciding that some/many/most of
those calculations were unnecessary.

Your IIf is missing the comparison:

=IIf(Nz[SumOfAClCkAmt],0)=0,[SumOfANonClCkAmt],[SumOfAClCkAmt])


znibk wrote:
Since the SQL view of the query was created automatically as I used the
design view, and I'm not familiar with the WHERE clause, do I just replace
the word HAVING in the SQL view with WHERE, or is there more to it than that?

Also, my IIF statement is returning 0, so I've something wrong, any ideas?
=IIf(Nz[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])


znibk wrote:
Thank you. I'm learning about this posting business! Although you may not
think so after reading this post.

I have not gone through everything yet. I wanted to address the Description
Text Boxes and Control Source.
1st issue, I moved it out of the Detail Section into a Description Header.
2nd issue, As I was getting everything ready for you, I noticed the Control
Source for the amount of one check was exactly the same as was in the Report
Footer for the sum of all the expense checks.

That steams from the Record Source for my SubRpt_AExpenses, which is
SumQry_ArvestClientAndNonClientExpenses
It is not correct, I do not think. the reason I think it is not correct is
because of what I said above. Here is the SQL view.

SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description
HAVING (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));

I'm not sure what I was thinking when I did that except that I did not want
to stack [SumOfAClCkAmt] on top of [SumOfANonClCkAmt] (as one or the other is
always going to be null or 0) beside the Control Source Description.

What I'm saying is that I want a line which would be a total of , for
example, NonClient Telephone Expenses 95.00
Petty Cash 45.00

The only way I knew to do it without stacking, was to have a field which
would stand for both types of checks.

I really hope that makes sense and you can tell me a better way.

Perhaps the better way would be to make the Control Source =IIf(Nz
[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])


:
I think your =IIf( . . . ) idea might be the easiest.

Note that you should change the query to use WHERE instead
of HAVING. The HAVING clause is for checking if an
aggregated value (e.g. Sum) is acceptable, but it is nowhere
near as efficient as the WHERE clause for ordinary values.

 

[Marshall Barton]

Ah ha! You really have changed the names od the subreport
***controls*** See below.

As for your report picking up more data than you want.
After fixing the IIf problem, you should put the report
aside. Then just work with the report's record source query
by itself until you get it to return only the records you
want OR you figure out what the report is supposed to do
with the records you forgot to deal with.
--
Marsh
MVP [MS Access]

Removing the semi colon did the trick.

Here is my main report Detail Section:

Name: TxtBegBalTot
Control Source: ABegBal

First Subreport
SubRpt_AInc
Name: TxtIncomeSum

This name is totally unexpected. I have no idea why you
named the subreport control to make it look like a text box
name, but the IIf uses the same name as the SourceObject so
I think you can clear up this misunderstanding if you change
the name of this subreport control to:
Name: SubRpt_AInc

Source Object: Report.SubRpt_AInc

Second Subreport
SubRpt_AExpenses
Name: TxtExpenseSum

Same as above problem. Change this name to:
Name: SubRpt_AExpenses

Source Object: Report.SubRpt_AExpenses

First IIF text box:
Name: TxtRunInc
Control Source:
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Report.TxtIncomeSum,0)

Second IIF text box:
Name: TxtRunExp
Control Source:
=IIf(SubRpt_AExpenses.Report.HasData,SubRpt_AExpenses.Report.TxtExpenseSum,0)

Both of these should(?) work after you change the names of
the subreport controls.

Both are set to Running Sum: Over All

Report Footer:
=[TxtBegBalTot]+[TxtIncomeSum]-[TxtExpenseSum]

All information was copied and pasted.

Marshall, I think I might have found part of the problem??

I was concentrating so hard on these text boxes that I did not notice there
were two other pages that showed up. Each had a different beginning balance
but the income and expenses were the same.

Remember, we talked about the fact that the Subreports were linked to the
main report using the MonthYear.

The frmDates opens the report with the dates txtStart and txtEnd, which
gives us, of course, 8/1/07 ; 8/31/07 When in actual fact, I think we may
need a frmMonthYear (or frmRefDate) where I can enter, ie Aug 2007
I created a frmRefDate with the unbound text box, however, I forced the
query builder to SELECT tblMONTHYEAR.MONTH_YEAR, tblMONTHYEAR.MonthYear FROM
tblMONTHYEAR;

Unfortunately, it did not solve the problem of additional reportswith
different beginning balances.

Hope you can find what's wrong, I've looked at each one so closely, I feel
my eyes are crossed.

I forgot to tell you to remove the semi colon.

The #Name is because there is a name in the IIf that Access
can not find. I need to see it to make sure the syntax is
correct or if something else is wrong. Because you have
made this mistake before, the first thing you should check
is that any square brackets enclose only a single name. If
that's not it, double check that you are using subreport
***control*** names, which might be different from the
actual name of the report you are using as a subreport.

Yea, Marshall, you are great. I knew it was probably some simple little thing
in my IIF statement, but you can tell by my other post, that I had to keep
trying while I waited.

Adding the comparison "=0" worked great; everything looks great.

But, I did what you said to the SQL view on the query, changed the word
HAVING to WHERE and moved the GROUP statement below it. I got this message
when I tried to move it back tothe Design View.

"The LEVEL clause includes a reserved word or argument that is misspelled or
missing, or the punctuation is incorrect.

Here is the SQL View:
SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
WHERE (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description


:
Not quite, change the word HAVING to the word WHERE in SQL
view and then move the GROUP BY clause down after the WHERE
clause. The converter from the query design view to the
corresponding SQL statement is too dumb to make the
determine when to use the appropriate clause. As I said
before, HAVING will work, but it can be much less efficient
than WHERE. This is because WHERE filters out the undesired
records before doing all the calculations, but HAVING does
all the calculations before deciding that some/many/most of
those calculations were unnecessary.

Your IIf is missing the comparison:

=IIf(Nz[SumOfAClCkAmt],0)=0,[SumOfANonClCkAmt],[SumOfAClCkAmt])


znibk wrote:
Since the SQL view of the query was created automatically as I used the
design view, and I'm not familiar with the WHERE clause, do I just replace
the word HAVING in the SQL view with WHERE, or is there more to it than that?

Also, my IIF statement is returning 0, so I've something wrong, any ideas?
=IIf(Nz[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])


znibk wrote:
Thank you. I'm learning about this posting business! Although you may not
think so after reading this post.

I have not gone through everything yet. I wanted to address the Description
Text Boxes and Control Source.
1st issue, I moved it out of the Detail Section into a Description Header.
2nd issue, As I was getting everything ready for you, I noticed the Control
Source for the amount of one check was exactly the same as was in the Report
Footer for the sum of all the expense checks.

That steams from the Record Source for my SubRpt_AExpenses, which is
SumQry_ArvestClientAndNonClientExpenses
It is not correct, I do not think. the reason I think it is not correct is
because of what I said above. Here is the SQL view.

SELECT qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description,
Sum(qry_ClientAndNonClientExpenses_All.AClCkAmt) AS SumOfAClCkAmt,
Sum(qry_ClientAndNonClientExpenses_All.ANonClCkAmt) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
GROUP BY qry_ClientAndNonClientExpenses_All.MonthYear,
qry_ClientAndNonClientExpenses_All.Bank,
qry_ClientAndNonClientExpenses_All.Whoes,
qry_ClientAndNonClientExpenses_All.Description
HAVING (((qry_ClientAndNonClientExpenses_All.Bank)="Arvest"));

I'm not sure what I was thinking when I did that except that I did not want
to stack [SumOfAClCkAmt] on top of [SumOfANonClCkAmt] (as one or the other is
always going to be null or 0) beside the Control Source Description.

What I'm saying is that I want a line which would be a total of , for
example, NonClient Telephone Expenses 95.00
Petty Cash 45.00

The only way I knew to do it without stacking, was to have a field which
would stand for both types of checks.

I really hope that makes sense and you can tell me a better way.

Perhaps the better way would be to make the Control Source =IIf(Nz
[SumOfAClCkAmt],0),[SumOfANonClCkAmt],[SumOfAClCkAmt])


:
I think your =IIf( . . . ) idea might be the easiest.

Note that you should change the query to use WHERE instead
of HAVING. The HAVING clause is for checking if an
aggregated value (e.g. Sum) is acceptable, but it is nowhere
near as efficient as the WHERE clause for ordinary values.