Still have hope but need more help. First Question, more to follow

Duane,

Here is what I think this "=rptctrlname.Report.txtTotal" means and here are
the places I've tried it with the same results "Invalid Syntax . . . operand
without operator."

I think it means to put the name of my main (parent) report "rpt_Financial
Statement.Report.txt Total

Thinking it would belong in the Report Footer as grand totals generally do,
I put it on the main report. I reread it and relized it must go on one of the
subreports. As the subrpt_expense comes last, I put =rpt_Financial
Statement.Report. txt Total. So, I put the [] around it. I got a blank spot
on my report. I wondered if that "txt Total" means I would be putting an
amount there? That is not what I want. I want the Ending Balance to be
calculated by the report.


"Duane Hookom" wrote:

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
add data?

Thanks again for the instruction to put the sub-reports in the Detail
Section and please help me out with the other problems I’m having. k


"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
your advise and Googled it. The articles I read made sense, but I do not
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 a
[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
had any luck.

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:

Please:
- 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:

I'm not sure how to be much more explicit. Let's start with your subreports
that are showing the correct information. You must have a Report Footer
section with a text box in it that calculates the subreport total value that
you want to display in your main report. Name this text box "txtExpSum" on
your expense subreport and t"txtIncSum" on your income subreport. This should
be easy.

Then add a text box on the main report in the same section that contains the
two subreports. The control sources of these text boxes would use the name of
your subreport control (srptExpense for instance)
=srptExpense.Report.txtExpSum
and
=srptIncome.Report.txtIncSum

This should display the totals from your subreports on your main report.

I was trying to get you to create a form "frmDates" and add text boxes with
names:
txtStart
and
txtEnd
Your main report's record source could then use a where clause like:
" WHERE [YourDateField] Between Forms!frmDates!txtStart and
Forms!frmDates!txtEnd"

Make sure the "Show" check box is un-checked in the query view.
--
Duane Hookom
Microsoft Access MVP


"znibk" wrote:

Duane,

Here is what I think this "=rptctrlname.Report.txtTotal" means and here are
the places I've tried it with the same results "Invalid Syntax . . . operand
without operator."

I think it means to put the name of my main (parent) report "rpt_Financial
Statement.Report.txt Total

Thinking it would belong in the Report Footer as grand totals generally do,
I put it on the main report. I reread it and relized it must go on one of the
subreports. As the subrpt_expense comes last, I put =rpt_Financial
Statement.Report. txt Total. So, I put the [] around it. I got a blank spot
on my report. I wondered if that "txt Total" means I would be putting an
amount there? That is not what I want. I want the Ending Balance to be
calculated by the report.


"Duane Hookom" wrote:

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
add data?

Thanks again for the instruction to put the sub-reports in the Detail
Section and please help me out with the other problems I’m having. k


"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
your advise and Googled it. The articles I read made sense, but I do not
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 a
[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
had any luck.

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:

Please:
- 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

Duane,

I failed the first two. I have tried entering them in the Report Footer as
instructed as "txtExpSum," [txtExpSum] inside the txt box, txpexpsum, expsum
and the same for the income subreport. I get error message invalid control .
.. no such field."

Maybe I'm just too tired. It is almost midnight and I've had a full day, as
I'm sure you have, but my eye-lids are falling down and my eyes are rolling,
and my vision is getting even more blurry. I can't even imagine going on to
the next set of instructions if I've failed the first two "easy ones. Maybe
we should just get me the dunce hat and let me wear it.

I'll try harder tomorrow after mass and then move to the next set of
instructions.

Christ's blessings, and enjoy the part of Sunday you get to be home.

K

"Duane Hookom" wrote:

I'm not sure how to be much more explicit. Let's start with your subreports
that are showing the correct information. You must have a Report Footer
section with a text box in it that calculates the subreport total value that
you want to display in your main report. Name this text box "txtExpSum" on
your expense subreport and t"txtIncSum" on your income subreport. This should
be easy.

Then add a text box on the main report in the same section that contains the
two subreports. The control sources of these text boxes would use the name of
your subreport control (srptExpense for instance)
=srptExpense.Report.txtExpSum
and
=srptIncome.Report.txtIncSum

This should display the totals from your subreports on your main report.

I was trying to get you to create a form "frmDates" and add text boxes with
names:
txtStart
and
txtEnd
Your main report's record source could then use a where clause like:
" WHERE [YourDateField] Between Forms!frmDates!txtStart and
Forms!frmDates!txtEnd"

Make sure the "Show" check box is un-checked in the query view.
--
Duane Hookom
Microsoft Access MVP


"znibk" wrote:

Duane,

Here is what I think this "=rptctrlname.Report.txtTotal" means and here are
the places I've tried it with the same results "Invalid Syntax . . . operand
without operator."

I think it means to put the name of my main (parent) report "rpt_Financial
Statement.Report.txt Total

Thinking it would belong in the Report Footer as grand totals generally do,
I put it on the main report. I reread it and relized it must go on one of the
subreports. As the subrpt_expense comes last, I put =rpt_Financial
Statement.Report. txt Total. So, I put the [] around it. I got a blank spot
on my report. I wondered if that "txt Total" means I would be putting an
amount there? That is not what I want. I want the Ending Balance to be
calculated by the report.


"Duane Hookom" wrote:

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
add data?

Thanks again for the instruction to put the sub-reports in the Detail
Section and please help me out with the other problems I’m having. k


"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
your advise and Googled it. The articles I read made sense, but I do not
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 a
[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
had any luck.

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:

Please:
- 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

Duane,

On the last thing, the frmDate, I did that. I did nt have the where clause,
but I kept getting the invalid control source on the frmDate. Also, does it
not make a difference that my parameter is not from a start time to an end
time but just one field name entry, in this case, "Jul 2007" k

"Duane Hookom" wrote:

I'm not sure how to be much more explicit. Let's start with your subreports
that are showing the correct information. You must have a Report Footer
section with a text box in it that calculates the subreport total value that
you want to display in your main report. Name this text box "txtExpSum" on
your expense subreport and t"txtIncSum" on your income subreport. This should
be easy.

Then add a text box on the main report in the same section that contains the
two subreports. The control sources of these text boxes would use the name of
your subreport control (srptExpense for instance)
=srptExpense.Report.txtExpSum
and
=srptIncome.Report.txtIncSum

This should display the totals from your subreports on your main report.

I was trying to get you to create a form "frmDates" and add text boxes with
names:
txtStart
and
txtEnd
Your main report's record source could then use a where clause like:
" WHERE [YourDateField] Between Forms!frmDates!txtStart and
Forms!frmDates!txtEnd"

Make sure the "Show" check box is un-checked in the query view.
--
Duane Hookom
Microsoft Access MVP


"znibk" wrote:

Duane,

Here is what I think this "=rptctrlname.Report.txtTotal" means and here are
the places I've tried it with the same results "Invalid Syntax . . . operand
without operator."

I think it means to put the name of my main (parent) report "rpt_Financial
Statement.Report.txt Total

Thinking it would belong in the Report Footer as grand totals generally do,
I put it on the main report. I reread it and relized it must go on one of the
subreports. As the subrpt_expense comes last, I put =rpt_Financial
Statement.Report. txt Total. So, I put the [] around it. I got a blank spot
on my report. I wondered if that "txt Total" means I would be putting an
amount there? That is not what I want. I want the Ending Balance to be
calculated by the report.


"Duane Hookom" wrote:

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
add data?

Thanks again for the instruction to put the sub-reports in the Detail
Section and please help me out with the other problems I’m having. k


"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
your advise and Googled it. The articles I read made sense, but I do not
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 a
[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
had any luck.

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:

Please:
- 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