# RE: Still have hope but need more help. First Question, more to fo

Do we know if the subreports are showing what you expect? Do you have report
totals on your subreports that can be referenced from your main report? Do
you understand how to reference the value of a control on a subreport"
=rptctrlname.Report.txtTotal

Can you just humor me and open your report's record source and set the
criteria under the date field to the expression that I have suggested? (make
sure the form is open first with some dates entered). View the datasheet and
you should notice the dates are being used as a filter.

--
Duane Hookom
Microsoft Access MVP

"znibk" wrote:

Duane,

They say third time is charm. I've tried replying twice tonight. Here I go
again and I do hope I catch you before you go fishing.

The formula was for the ending balance. Sorry I neglected to post that
information; thought I had. I was thinking I had to group the beginning
balance and income together before I substracted the expenses. I tried your
formula and it did not work. I think it may have something to do with the
fact that the beginning balance is on the main report, income on the 1st
sub-report, and expense on the 2nd sub-report.

I can make a frmDates with text boxes for date fields. However, I don't know
what I'm supposed to do with it after I make it. Further instructions are
needed. Sure hope you are still there, God preforms miracles every day. k

"Duane Hookom" wrote:

No, I didn't miss but if you don't understand my reply, you will miss me
until at least Saturday evening since I will be gone fishin'.

You really need to kick your application up a notch and create a form named
frmDates and add a couple text boxes. You then can open this form, enter some
date values, and then open your reports the with dates values as criteria.

Where and why is this control source used:
=(Sum([ABegBal]+(Sum([ATotInc])-Sum([ATotExp]))))
Shouldn't it be"
=Sum([ABegBal])+Sum([ATotInc])-Sum([ATotExp])

--
Duane Hookom
Microsoft Access MVP

"znibk" wrote:

Good evening Duane,

Did you miss me? I had already created a main report and two sub-reports and
was debating on where to put the sub-reports. Your instruction to put them in
the Detail Section worked great. Thank you.

However, I used the dreaded parameter query to create all three reports.
And, when I try to open the main report, it asks me three times to enter the
Month Year parameter. Also, I receive the following error message when I try
to open the report “Cannot have aggregate function in expression
=(Sum([ABegBal]+(Sum([ATotInc])-Sum([ATotExp])))) I don’t understand why it
will allow me to leave the text box then give me the error message when
trying to open the report. What have I entered incorrectly? And, how should
it be. I tried several combinations, the above being the last and least
complicated.

And I looked at all properties/criteria in the table and form and cannot
determine where/which property/criteria I would be “Assuming Form: frmDates
with text boxes txtStart and txtEnd you would set the criteria under a date
field to: Between Forms!frmDates!txtStart And Forms!frmDates!txtEnd” When I
learn how to do this, is it going to just open a report to a certain date
range, or will it also open the form to that date range every time I open it

Thanks again for the instruction to put the sub-reports in the Detail

"Duane Hookom" wrote:

Create a main report that has only the unique banks since this is the value
you want to group Expenses in one column and income in the other. There
should be just one record per bank.

Create a report based on the expenses table and a report based on the income
table. Place these reports in the detail section of the main report and set
the Link Master/Child properties to the Bank field.

You can use controls on a form to filter a query without writing any code.
Assuming Form: frmDates with text boxes txtStart and txtEnd you would set the
criteria under a date field to:
Between Forms!frmDates!txtStart And Forms!frmDates!txtEnd
No code required.
--
Duane Hookom
Microsoft Access MVP

"znibk" wrote:

Good evening Duane,

Don't think you are going to be happy with my answers but here goes. I have
been using Microsoft Help to look for information on UNION Queries but took
think the use of one will help me. I looked up in the Discussion Group, the
use of filters in a form, I am completely confused. I do not know what you
mean when you say "IMHO parameter prompts aren't acceptable user interface."
(I do not write code) Please explain.

You asked me in one of your earlier posts to tell you the tables I have and
the field names. I have an Income Table, tbl_Income. The fieldnames are:
[RefMo] (Number]
[IncDate] (Date/Time)
[Bank] (Number)
[Sales] (Currency)
[ACD] for A Cash Donation (Currency)
[Contributor] (Number)
[IncType] (Number)
[OS] for Other Sales (Currency)
[IKC] for In-Kind Contributor (Number)
[Donation] (Text)
[IKValue] (Currency)

for all of my (Number) fields above, I have tables listing the choices, then
I have each FieldID Joined to the tbl_Income in a query.

Next major thing is Expenses so, tbl_Expense with the following fields:
[Bank] (Number)
[RefMo] (Number)
[CkDate] (Date/Time)
[CkNo] (Text)
[Bsn] (Number)
[Whoes] (Number)
[ExpType] (Number)
[Description] (Number)

Again, for all of my (Number) fields above, I have tables listing the
choices, then I have each FieldID Joined to the tbl_Expense in a query.

I have two forms for entering the information. On the forms, I have used
Combo Boxes, and set the Row Source (using the . . . icon) to reference the
respective tables (John Vinson, MVP) taught me to do that rather than using
the Lookup Wizard. Everything works great. When I need to edit something, I
do so on the form. However, as stated above, I don't write code and am
completely lost when you refer to using a filter on the form rather than a
parameter query for my reports.

From the Income information, I ultimately want a report which shows me, for
a certain time period, (maybe something else later) the day of the month
something was either sold, or donated and by whom and what it was. I also
want a report which shows me, in columns which are side by side on the same
report (and as you said, there are more records in one field than another) I
can build the report using the parameter query but the columns appear off
set. I have been trying to get a subreport to work, but as of yet, have not

From the Expense information, I have successfully made two different
reports. One which shows all information about one particular type of
expense; and one report which shows the total expense amount for each
[description] and each [ExpType]

What I need to do with the information is to combine the tbl_income and
tbl_expense for each bank, into a financial statement.

Bank [A]
[ABegBal] which would come from each banking information table
Income:
[Asales]
[Aos]
[Aoi]
[Acd]
Total Income: =Sum(Nz(Asales],0) etc.
Expenses:
[Whoes]
[Type] TotalType
Total Expenses:
[AEndBal]

I would like to put this information in side by side columns on the same
report page. I think the sub-report would be the thing to use, if I can just
get one to work. I am still doing research and expermentation on that.

I do hope this tells you what my tables are, examples of my field names, and
what I want from my reports. I really hope it does not confuse you more, and,
I hope you can help me. But, Duane, please remember I do not write code. I
can, on some occasions, make sense out of an SQL statement, but sometimes
not. I am afterall, a beginner. k

"Duane Hookom" wrote:

Check Help or google on UNION Queries. There is a ton of information.
There is also lots of information on how to use a control (or controls) on a
form to filter a query. IMHO parameter prompts aren't acceptable user
interface.

--
Duane Hookom
Microsoft Access MVP

"znibk" wrote:

Duane,

Sorry I'm making you confused. Perhaps if I knew exactly what a union query
was, it would help. Also, how am I to generate only a certain date span on a
report if I don't use a parameter query?

Anyway, you are off the hook for awhile, I have a meeting to go to and won't
be home for awhile. When I get back, I'll re-read everything you have said
closely and see what I am supposed to be doing.

Have a good evening and thanks for your help. k

"Duane Hookom" wrote:

- describe your data with table and field names
- tell us how you want to show this in a report
- stop using parameter prompt queries. Replace all prompts with references
to controls on forms
--
Duane Hookom
Microsoft Access MVP

"znibk" wrote:

Duane,

I sent the SQL view for the query. Hope it is helpful.

Here is the new problem. I have created a report showing the Cash
Contribution side of the report and a report showing the In-Kind Contribution
side. I found the sub-report icon. As with everything else, I seem to have
nothing in common with each query/report expcept the Month and Year. The
parent/child relationship does not seem to work. As stated earlier, I have
fewer In-Kind Donations than Cash Donations therefore using the other
parent/child relationships don't seem to work. Also, I have the Income date
used to generate the results by using Between [Enter Start Date] And [Enter
End Date] when I try to use "none" as the connecting field, the report just
keeps asking me for the start and end date over and over again.

Any suggestions on what to do for this problem? k

"Duane Hookom" wrote:

If your different groups are "Income" and "Expenses" I expect you could
create one or more subreports to get at your desired results. Do you have an
account number or similar that you want to group by? IOW what ties a group of
Income records with a group of Expense records? Are you using a union query?
If so, can you share the SQL view?

--
Duane Hookom
Microsoft Access MVP

"znibk" wrote:

Good afternoon Duane,

Yes, they are.

When I tried to combine my SumQry_MonthIncome with my SumQry_MonthExpenses,
up in the right hand corner appears a tag which says "Union Query." But, when
I enter the field names , I receive multiple returns of the entries. Does
that mean that I am ready to develop some sub-reports? Boy, oh boy, do I hope
so; otherwise I'm going to be doing some cutting and pasting at the copy
machine tonight or I'm going to enter my information quickly into Excel.

What do you think I'll be doing? k

"Duane Hookom" wrote:

Aren't your "groups" just different records from the same table/query?

Subreports are much like subforms. They are just reports that are placed on
other reports.

--
Duane Hookom
Microsoft Access MVP

"znibk" wrote:

Good morning Duane,

Sorry to take so long getting back.

Your first paragraph is right on--correct; the number of records in each
group varies. The good news is, I hope, is I have already put the information
in a query and am using that query as the Control Source for the report. In
the report, I have put the information for each column in the Detail section.
For the left side of the report, I have [CC] [ACD] for the right side of the
report, also in the Detail section, I have [IKC] [Donation] [IKValue]. All
information is there except for the fact it list all the information for the
Cash Contributors before it lists all the information for the In-kind
Contributors. I need the columns to be side by side !! equally regardless if
one column has more data than the other.

And, unfortunately, you have completely lost me on the second paragraph;
.

• Follow-Ups:
• References: