Subtract two totals from 2 subreports in main report of access

[scottrmeredith]

I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.

On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.

On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.

I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.

Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.

=[rptP&LExpensesOverview]![AccessTotalsAmount]

=[rptP&LSalesOverview]![AccessTotalsTotal Sales]

As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).

So how can I accomplish this?

[scottrmeredith]

I forgot I wanted to put this in also for reference:

I am trying this to calculate the difference between the two totals.

=Sum([rptP&LSalesOverview]![AccessTotalsTotal Sales]-[rptP&LExpensesOverview]![AccessTotalsAmount]

I have tried using the names of the two text boxes in the main report also with no luck.

[twinnyfo]

Have you tried (in the main report) referring explicitly to the controls?

Me.SubReportName.ControlName

You have to be very careful when referring to summed controls. I always use a prefix for the control just to make sure I am referring to the Control and not the control source itself (e.g. txtAccessTotals Amount).

Also, as a pointer, I always try to avoid any spaces and special characters in any of my field/control names ("&"). These can sometimes cause problems if you are not very careful when referring to these fields.

Hope this helps.

[scottrmeredith]

Here is the solution that worked for anyone else later seeing this forum. In my main report text box controls I used the following in the control source of the two controls to bring the sub-form totals into the main form.

=[Reports]![rptP&LOverView]![rptP&LSalesOver view].[Report]![AccessTotalsTot al Sales]
and
=[Reports]![rptP&LOverView]![rptP&LExpensesO verview].[Report]![AccessTotalsAmo unt]

With that in there I was able to use the names of the two controls to do the math in the footer of the main report
=[TotalSalesMain]-[TotalExpenseMai n]

I still don't quite understand the syntax completely of the two references for the control source (the order the the two "Reports" and "Report" references in the formula, but will research that later.