Linking reports?

[Japandave]

How would I go about linking reports? I have to make monthly invoices and I am using reports to do these, but I need to carry over the last months invoice total to the the next months report in the header. I am also not sure how to get the total of the current report and put it in the header? Any ideas?

[June7]

Reports aren't linked.

I am not sure about the first requirement.

The second is an aggregate calculation that can be probably done with a Sum() calculation in a textbox. Would have to know more about your data and report structure.

[Amicron]

You could grab last month's total with a DSUM function in a text box, like June7 said. Set the control source to something like:

=DSUM("InvoiceTotal","InvoiceT","OrderDate>=#1/1/2011# AND OrderDate<=#2/1/2011#")

Of course, substitute in the proper field and table names - and set the dates accordingly.

I've got a tutorial that talks about DLOOKUP which is a very similar function. You can find it here: http://599cd.com/XMQ51R

RR

[Japandave]

Reports aren't linked.

I am not sure about the first requirement.

The second is an aggregate calculation that can be probably done with a Sum() calculation in a textbox. Would have to know more about your data and report structure.

How about a macro or VBA to pull the sum total from the other report?

I figured the second problem I think, I just used a text box with this =Sum([Total]) and placed it in the header in design mode.

[June7]

I don't know what you mean by 'pull the sum total from the other report'. Don't you have only the one report object that is simiply filtered to different date periods? What AccessLearningZone suggested is probably what needs to be done to get a summation from previous period. Trick with a DSum is it will have to include criteria to make sure the sum is aggregated by customer, not for the whole table. Do you want to provide project for analysis? Make copy, remove confidential data, run Compact & Repair, zip if still large, attach to post.

[Japandave]

I am trying to create an invoice for each month and save it. I need to keep all my invoice for 5 years by law.

When I say "sum total" I was meaning getting to previous months invoice final total and populating it in a text cell on the next months report, so you can see the previous invoice total.

Unless there was a formula that could sum the total cost of orders made for the previous month and have it on the current report? That way I would do as you say and just use one report and then specify start date/end date. I am currently using this formula in my query, >=[Start Date] And <=[End Date]. Would it be possible to do the same in a text box in the header, only have it sum the total costs of the orders in that time period?

[June7]

Summing the data for the records retrieved in the report is simple. You seemed to have resolved that issue.

Getting the total value of records prior to this report's period is not so simple. Try this, create a field in the Date query with an expression:
PreviousPeriod: DSum("Row2","Sheet1","Row1<#" & [Start Date] & "#"). Put a textbox in report header or footer and bind it to this constructed field. Be aware that any other criteria applied to the report, such as customer ID, will also have to be included in the DSum criteria.

Additional note, I never use input parameter prompts, can't validate user input. I have users input value to a form, then have the query reference the controls for parameters.

[Japandave]

Ok, I have never created a form before. but I was able to do what you said and get the form linked to the Query and the Report.

However, I now don't know where to put the DSUM? I tried putting it below the other code in the Query, but that did not work. Not mention I am not sure what syntax of that formula is. Anyway, here is the file.

[Japandave]

You could grab last month's total with a DSUM function in a text box, like June7 said. Set the control source to something like:

=DSUM("InvoiceTotal","InvoiceT","OrderDate>=#1/1/2011# AND OrderDate<=#2/1/2011#")

Of course, substitute in the proper field and table names - and set the dates accordingly.

I've got a tutorial that talks about DLOOKUP which is a very similar function. You can find it here: http://599cd.com/XMQ51R

RR

Thanks for that link, I missed it earlier. That makes the syntax for the DSUM very understandable.


@June7, I was able to get that forumla into the text through the builder in the properties box on the Data tab for the text box. But it is returning nothing of what I want.

[June7]

Need to change Date query to reference the form textboxes for the date range criteria:
Between Forms!DateForm!StartDate And Forms!DateForm!EndDate

The RunQuery button is unnecessary unless you want it to open just to view it.

You have a textbox in report header that sums the records, might want that in the footer. Use this expression in field row of query:
PrevTotal: DSum("[Row8]*1.2","[Sheet1]","Row1<#" & [Forms]![DateForm]![StartDate] & "#")
ControlSource for textbox in report header: PrevTotal

Does that get you any closer?

[Japandave]

Need to change Date query to reference the form textboxes for the date range criteria:
Between Forms!DateForm!StartDate And Forms!DateForm!EndDate

The RunQuery button is unnecessary unless you want it to open just to view it.

Not quite sure how to do that?

You have a textbox in report header that sums the records, might want that in the footer. Use this expression in field row of query:
PrevTotal: DSum("[Row8]*1.2","[Sheet1]","Row1<#" & [Forms]![DateForm]![StartDate] & "#")
ControlSource for textbox in report header: PrevTotal

Does that get you any closer?

Which row should I be putting this expression in, in the field box? And are you talking about the text box on the left in the header?

Again , I wish to thank you for all your help thus far, it is much appreciated.

[June7]

Between Forms!DateForm!StartDate And Forms!DateForm!EndDate
goes in the criteria row of query for the date field.

PrevTotal: DSum("[Row8]*1.2","[Sheet1]","Row1<#" & [Forms]![DateForm]![StartDate] & "#")
yes, this goes in a field row of query to create a field called PrevTotal.

[Japandave]

Ok, I did that, I think. But now it is not linking to form. I have attched a file to show what I did.

Cheers

[June7]

Which report is the issue?

The 20% report will work. Problem is cursor needs to leave the form's EndDate box after enter date. Must hit Enter key.

The other report does not have the revised date criteria in the query.

[Japandave]

Which report is the issue?

The 20% report will work. Problem is cursor needs to leave the form's EndDate box after enter date. Must hit Enter key.

The other report does not have the revised date criteria in the query.

I hit the enter key and it just moves to the start date box, it still has the start date in it though.

And when I run the 20% Report (The other report is invalid ,I should have deleted it) it sums the whole Tables data. Not in between those dates.

And I am not sure how those formulas are going to get me the previous months invoice total. Would I need 4 boxs on the form, 2 to get the current invoices total and another 2 to get the previous invoices total?

Edit: Could you also tell me what this symbol represents in the formula?

#