For Marshall Barton

[znibk]

Marshall,

Do you every want to give up? I'm getting to that point, but I'm not a
quitter.

I changed the Names because what was there was not working, and the
expression in the Report Footer is:
=([TxtBegBalTot]+[TxtIncomeSum]-[TxtExpenseSum])
So, I thought, maybe the name of the SubRpt_AInc should be TxtIncomeSum, etc.

At any rate, I still get the name error.

I will continue to look.

I had set Parameters in my queries to return just the MonthYear I wanted.
That was what I'd always been taught to do, and in all the Access course
books I have, it says the same thing. However, when I ran the report, it kept
asking me for the same parameter over and over again. Duane Hookman, MVP,
said I was never, under any circumstances to set parameters in a query. He
said I was to develop a frmDates with two unbound text boxes to do that.
Trouble is, I use [RefDate] with Data Type as Number. I then creaed a Table
MONTH_DATE and have the month and year of the fiscal months/year we have ie,
Oct 2006, Nov 2006 . . . Sep 2007. I will then add to it as time passes.
(Just thought of something--Would it be possible to create a Date/Time format
/ input mask /whatever it is called as mmm/yyyy with input mask as
999/00;;0_) Is that possible?

Also, the original name that Access entered for the Name of the subreports
was:
Name: SubRpt_AInc.Report.TxtIncomeSum
Source Object: Report.SubRpt_AInc

Name: SubRpt_AExpenses.Report.TxtExpenseSum
SourceObject: Report.SubRpt_AExpenses

I Don't Know!!!!!!!!!!!!!! Do you?

Christ's blessings,
k

P. S. I shall be very happy to get to the point I get to put aside the
report because it is correct

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.


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

 

[znibk]

Marshall,

You have to look at the times of the day to make any sense of these
postings, but here goes.

I HAVE THE ENDING BALANCE. I AM SO EXCITED. YOU HAVE NO IDEA. I'm not
yelling this. I can't underline it to give it emphasis. My expression when a
number popped up at the end of the page was "Oh my God, Oh my God." I
immediately got a pen and paper to see if the answer was correct. It was. It
was.

Thank you, thank you, thank you. I can't say it enough times.

Please check back with me though because I now have to add the same
information for Bank L to the right side of the paper. I'm imagining that
will mean several subreports. I can't imagine that I would be able to put
additional subreports into the same detail section of the main report.

I shall start on that tomorrow sometime.

WOW, I can't believe after all this time, since May, I am going to get to go
to bed with an ending balance on my report. It is fantastic. God does indeed
work miracles for those who do not give up. I need to and will have to
remember that in the months ahead. We have many family trials ahead of us.

God Bless you and yours always,
k

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.


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

 

[Marshall Barton]

Marshall,

You have to look at the times of the day to make any sense of these
postings, but here goes.

I HAVE THE ENDING BALANCE. I AM SO EXCITED. YOU HAVE NO IDEA. I'm not
yelling this. I can't underline it to give it emphasis. My expression when a
number popped up at the end of the page was "Oh my God, Oh my God." I
immediately got a pen and paper to see if the answer was correct. It was. It
was.

Thank you, thank you, thank you. I can't say it enough times.

Please check back with me though because I now have to add the same
information for Bank L to the right side of the paper. I'm imagining that
will mean several subreports. I can't imagine that I would be able to put
additional subreports into the same detail section of the main report.

I shall start on that tomorrow sometime.

WOW, I can't believe after all this time, since May, I am going to get to go
to bed with an ending balance on my report. It is fantastic. God does indeed
work miracles for those who do not give up. I need to and will have to
remember that in the months ahead. We have many family trials ahead of us.

Hallelujah!

This is great to hear, but could you explain what roadblock
you had to overcome after your previus post?