For Marshall Barton

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_AExpen ses.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






"Marshall Barton" wrote:

znibk wrote:
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]


"Marshall Barton" wrote:

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.


"Marshall Barton" wrote:
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

"Marshall Barton" wrote:

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_AExpen ses.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.

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]


znibk wrote:
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_AExpen ses.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_AExpe nses.Report.TxtExpenseSum,0)
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Repor t.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.

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)="Arves t"));

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


"Marshall Barton" wrote:

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]


znibk wrote:
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_AExpen ses.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_AExpe nses.Report.TxtExpenseSum,0)
=IIf(SubRpt_AInc.Report.HasData,SubRpt_AInc.Repor t.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 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.ANonClCkAm t) 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)="Arves t"));

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.

--
Marsh
MVP [MS Access]

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


"Marshall Barton" wrote:

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.ANonClCkAm t) 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)="Arves t"));

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.

--
Marsh
MVP [MS Access]

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]


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.ANonClCkAm t) 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)="Arves t"));

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])


"Marshall Barton" wrote:
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,

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

"Marshall Barton" wrote:

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.ANonClCkAm t) 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)="Arves t"));

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.

--
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)="Arves t"));
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.


"Marshall Barton" wrote:

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]


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.ANonClCkAm t) 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)="Arves t"));

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])


"Marshall Barton" wrote:
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.

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]


znibk wrote:
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.ANonClCkAm t) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
WHERE (((qry_ClientAndNonClientExpenses_All.Bank)="Arves t"));
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.


"Marshall Barton" wrote:

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.ANonClCkAm t) 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)="Arves t"));

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])


"Marshall Barton" wrote:
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,

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_AExpen ses.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





"Marshall Barton" wrote:

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]


znibk wrote:
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.ANonClCkAm t) AS SumOfANonClCkAmt,
[SumOfAClCkAmt]+[SumOfANonClCkAmt] AS ATotExp
FROM qry_ClientAndNonClientExpenses_All
WHERE (((qry_ClientAndNonClientExpenses_All.Bank)="Arves t"));
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.


"Marshall Barton" wrote:

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.ANonClCkAm t) 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)="Arves t"));

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])


"Marshall Barton" wrote:
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.