• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

Pass date range from report to subreport in Access if subreport is needed

I realize that this has been asked before, but I am having troube following the answers to someone else's data.  I have a report that has a date range picker using a form.  The report is laid out like this:

Date     Jan 1
     Location A
           Stop Action   2
            Start Action  3
     Location B
           Stop Action   1            
           Start Action   4
           Adjust Action  2
 Date     Jan 2
     Location A
           Stop Action   1
            Start Action  7
            Adjust Action  8
      Location B
           Stop Action   1            
           Start Action   4
Total Actions =  33

I need to have the same data totaled by Action Type:
Stop Action   5
Start Action  18
Adjust Action 10
Total = 33

I made a subreport for the data totaled by action type.  I want the data picker to pass the dates to the subreport.  Better yet, is there a way to group the data so I do not need a subreport at all?

  • 7
  • 5
1 Solution

Poke at the report and poke at the query, and then ask away about what you don't understand

See attached
LindaOKSTATEAuthor Commented:
Thanks for making the report, very nice.  I was trying to simplify a little.  I redid the report to look more like mine.  I only used one table because the other table I am using isn't vital to this part.  The problem with totaling the actions using something like =sum([Stop]) and =Sum([Start]) is that it expects these actions and these only.  There are close to 300 actions that can show up.  I need something that will find the action type from the details and post that with a total number by date range disregarding location.  The dates are text in the table fields, not true dates.  So, this is where the subreport part comes in and the need to attach date(s) from the report to the subreport.

Well, apparently the report I was working on didn't save since it opened in a temp file.  Not happy.  Maybe you can understand my problem anyway.  
I can code, but I think we can avoid a code-heavy method.

This kick at the cat was utterly code-less.

<There are close to 300 actions that can show up>
That does necessitate a subform

Poke at the upload

The trick here is that the report and the subreport are NOT related on any field

"Huh???" you might say.  "How does it get it right then?"

The trick is the queries
Give them the same parameters, and one query(the main report) will give you the details, while the other (the subreport) will give you the totals

Now, you need a form for BOTH queries to get the 'dates' from and coerce the text to an actual date.

That's the next post and rev
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Now poke at the form and the queries.
The queries get the parameters from the form
The combo boxes, with their hidden column, take care of coercing the dates.

and it plays nice.

It ain't pretty, but it shows how to do it
LindaOKSTATEAuthor Commented:
OK.  Frustration mounting.  I have the report and the subreport.  Both work separately.  I had been running them off the same query, but copied and changed the name for the subreport query so I could have one with a hidden column.  I can get the subquery to show but inside the main report but I have to click the date picker again.  It actually asks me the date several times, over and over.  Is it wanting the date for every action in in the subreport maybe?
In Rev2, the queries for the report and subreport used the same FORM's parameters.  If you are being prompted for parameters, then the form is in design mode or not open, because if things are set up ok, neither query should prompt for parameters.  The report/subreport depend on the queries and the queries depend on the form to give them matching parameters.  That's how it hangs together.

Are you trying to avoid the use of a form in your production app?
LindaOKSTATEAuthor Commented:
This report is adapted from another report.  There is a date range form already built.  I just realized that there are two macros that run in the events on the report and subreport.on open and on close .  I'm sure this is somehow causing the multiple range pickers to keep popping up but not sure how.
Post the code and we'll have a look.
Post the SQL from your queries too
LindaOKSTATEAuthor Commented:
Well, I have tried so many things, I am not even sure what I have.  I will have to relook.  I tried to make it simpler, got rid of the form, the macros and just put in parameters,  Didn't work.  I can't post the code and SQL outright since it is part of a proprietary system.  So, not sure what to do next..
Tell me what you understand about Rev2.

I can comment the daylights out of it if you don't grasp what was behind it.
First, the form

Because you said you dates are stored as text, I made the combo boxes take care of coercing the text to dates.
The combo boxes have a hidden column--which is the coerced date, and show the text from the table.

The queries depend on the form.  They pull their parameters from the form.
I have refined them even more.  The queries now coerce the 'text' dates to date, and the parameters are against that column now
Note that each query is independent.  They pull stuff from the same table, but groupings and fields are not quite the same.

The report and subreport do not depend on each other.  There is no master/child pairing between them.
It is the fact that both queries pull from the same tables with the same parameters that makes them hang together.

You have some things to handle carefully because you are working with dates/text in query parameters
Text variables HAVE to be surrounded by quotes.
So if you leave the fields as text and pass parameters to the queries they have to be like
The bad thing is sorting and 'betweening' a text field
You'll get some odd effects--> 1/3/2011 will come between 1/1/2011 and 31/1/2011

So you need to coerce --> CDate(SomeString) is your friend
Dates, though, have to be surrounded by hashes

Now, sometimes Access plays nice about all this quoting and hashing stuff.
If you pass stuff in as form control values, it will usually do that stuff for you.

If you pass it in as variables form VBA code, or type it in manually, you HAVE to do the quoting and hashing yourself.

Now, here's Rev3.
Break it.  Try and make it more like the stuff you can't post, so it realistically mirrors your real problem while keeping proprietary what you need to.
Post it back to me broken, and I can start to see where you might be going wrong, or overlooking.
We'll bounce it back and forth til we get 'er cased :)
LindaOKSTATEAuthor Commented:
Here is what I would like to do.  I did download the totals-rev3 mdb. I have not looked at it closely yet. The sub-report for this is only part of the problem.  There are issues with the main report also.  The report is looking at shifts divided into time intervals.  all the intervals are on the main table.  this is not a problem for the first 2 shifts, but the 3rd shift crosses from one date to the next.  So, we are looking at making a temp table, then an update query and an append query to get the first part of the data from one day and the rest of the shift data from the following day.  While we are working on that I was trying to get ahead by getting the sub-report ready.  It turned out to be the headache you and I are now are now working on.  As I thought about it, I was thinking that the date range for the first report which takes into account data for two days will mess up the dates we want from the sub-report.  Then, I realized that if we do a make temp table that is updated and appended each time with the data needed only for that report, the sub-report can be run against the temp table and no date range will be needed for the sub-report since only data for that time period will be on it.  I propose to accept your solution as it stands now, with the higher points for all you help but not save it to the database.  Then after the main report is solidified and if I need help again for the sub-report I can re-post a similar question.   Does that sound like a good plan?

Thanks again,

You have grasped the main part of the solution
<the sub-report can be run against the temp table and no date range will be needed for the sub-report >

That's clearly where it lies.  
As long as the 'totals' query is running against the same data that the 'details' come from, you get a workable solution.
Come back to this question after you award points.
You'll see a link to 'create a related question'
If and when you need to do so, create a related question.
EE then emails all participants in this question that a related question has been created.

Til later!



Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now