Reports chosen from form with strwhere date - subreport?

[misscrf]

I have a reports form. On this form, the user chooses the report that they want, by the first option group. Then they choose a date option on the second option group. They can choose today, to enter a date, a date range, or all dates.

Then the "get report" command button opens the report, and uses the date option to filter the chosen report. If the user chooses today, the filter is for date(), if they choose to enter a date, there is a date control on the form that become visible and required to be filled in. Date reange shows 2 date controls, etc.

This works for all of my reports, except my latest. I have a statistics report, which show counts of what has been happening with this data. One of the activities in this data has sub activities, and therefore has a subreport. When I run the cumulative, or all dates, it runs great. If I choose any other date option, though, I do not get a correct filter on the subreport. It seems to pull just one date (indiscriminately).

I have tried adding the date field to the subreports query, tried making it a link between the reports, tried making it a criteria in the subquery, tried different groupings. Nothing seems to do it.

Can someone please help me figure out how to make the date filter for the main report, filter for the subreport?

I think one of the issues may be that while I use the date to filter, the report is just grouped to count, it does not sort or group on date.

Thanks!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Spenney]

What are the parent and child fields ? If the sub-report shows sub-activities for the main activity I assume it should be something along the lines of 'primary_activity_id', in which case the sub-report would only return sub-activity data for the main activity to which the date filter has already been applied ? Am I missing something ?

For applying a WHERE condition to a sub-report, this may help..

http://www.tek-tips.com/viewthread.cfm?qid=906088&page=1

 

[dhookom]

With subreport criteria, I either hard-code the form controls into the subreport's record source:

Between Forms!frmRptsFrm!txtStart and Forms!frmRptsFrm!txtEnd

Or, I will use DAO code to modify the SQL property of the query used in the subreport record source:

Dim strSQL as String
strSQL = "SELECT.... " & _
    "WHERE [DateField] BETWEEN #" & Forms!frmRptsFrm!txt...
CurrentDb.QueryDefs("qselForSubreport").SQL = strSQL

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

Thank you both for your responses. I am sorry that I have not replied right away, but I had to put other projects ahead of this unexpectedly. For the first question, the activity type (being the number in the activities table rather than the actual activity type name from the activitytype lookup table) is the parent/child link. It is this way, because for both of the reports, if I put the ActivityID field from the activities table, then the reports do not count my statistics properly.

For the second question, I cannot hard code the form, because the form allows the chooser to decide 1)what report (optiongroup) 2) what date (optiongroup/textbox(s)) and 3) preview the strwhere supplied by 1 and 2.

Thanks for your help. If you have any further suggestions, I would love to hear them. If you would like the sql of each query for (main and sub) the reports, I can give you those too.

Thank you again.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I thought I would post the SQL for the main and sub queries, behind the main and sub reports, in hopes that I might get further. I have still not solved this.

Thanks.


SQL for query behind Activity Statistics Report:

SELECT [ActType] & ": " & Count([ActType]) AS MyActivityCount, tblActivityType.ActType, Count(tblActivityType.ActType) AS CountOfActType, tblActivities.EnteredDate, tblActivities.ActivityType
FROM tblActivityType RIGHT JOIN tblActivities ON tblActivityType.ActivityTypeID = tblActivities.ActivityType
GROUP BY tblActivityType.ActType, tblActivities.EnteredDate, tblActivities.ActivityType
WITH OWNERACCESS OPTION;

----------------------------------------------------------------

SQL for query behind No Forwarding Satistics Report:

SELECT Count(tblActivities.ActivityType) AS CountOfActivityType, IIf(IsNull([ForwardType]),"No Reason Given",[ForwardType]) AS MyForwardType, tblActivities.ActivityType
FROM tblNoForward RIGHT JOIN tblActivities ON tblNoForward.NoForwardID = tblActivities.NoFwdRsn
WHERE (((tblActivities.ActivityType)=5))
GROUP BY IIf(IsNull([ForwardType]),"No Reason Given",[ForwardType]), tblActivities.ActivityType
WITH OWNERACCESS OPTION;

------------------------------------------------


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You don't have any date filtering on your second SQL. You can't pass any dates from the main report to the subreport since there is no date field in the sql.

You will need to use one of the two methods that I suggested 10 Nov 05 10:56.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

Im sorry. The problem is that I can't hard code this stuff. I use the report form to choose 1 of 6 reports. One of those reports is this statistics report, which has a subreport.

The report form also has a choice for a date option. The user may choose - Today, to enter a date, a date range, or all dates. If I hardcode the date range, and the user chooses a different option it won't work.

Anything else? I have tried to break this up many ways. One issue is that I have like 15 activities to report for these statistics. Of them, 3 have subactivities. A reason that activity happened. I am just having the worst time getting these subactivities to group and count right.

Do I do 3 subreports? Do I do 1 subreport with 3 groupings?

I have put the date field in that subreport, and it make the count go down. This is without a date filter on.

:-(

Please help!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Do you have text boxes on your "reports form" for start and end dates? If so, try something like:

SELECT Count(tblActivities.ActivityType) AS CountOfActivityType, IIf(IsNull([ForwardType]),"No Reason Given",[ForwardType]) AS MyForwardType, tblActivities.ActivityType
FROM tblNoForward RIGHT JOIN tblActivities ON tblNoForward.NoForwardID = tblActivities.NoFwdRsn
WHERE (((tblActivities.ActivityType)=5))
[b][red]AND [EnteredDate] BETWEEN Forms!frmReportForm!txtStartDate and Forms!frmReportForm!txtEndDate[/red][/b]
GROUP BY IIf(IsNull([ForwardType]),"No Reason Given",[ForwardType]), tblActivities.ActivityType
WITH OWNERACCESS OPTION

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

I stripped out the information that I couldn't post, and set up a db with tables, queries, the report form and the reports. If you have a chance to look at this, and think you can help, I would soooo appreciate it. I would really like to learn where I am going wrong.

You will see that I have a subreport for no forwarding reason, but I still have to get counts for updated address reason, and usps unable to update address reason.

Thanks for your help.

http://www.geocities.com/misscrf/statisticsreport.ZIP

I think the link will only let you save the zip, in order to get the db out.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I looked at your code, thank you for your responses and help. I think if you take a look at my setup you will see what I mean about the date option. The two controls on the report form are not necessarily used.

If I put them in the sql of the query, it will always want them. If you take a look at the sql of the reports form, I think the gstrwhere will explain things for you.

Then maybe you can explain things to me! (I hate being this lost on something that I am sure has an easy solution.)


Thank you again.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Use the controls on the report form. There is nothing that says that you can't put values in them. If Today is chosen put today's date in both controls. If some other date is chosen, put that date in both controls.

If you aren't too worried about performance, you could set gstrWhere to:

gstrWhere = "EnteredDate between #" & Me.txtFromChoose & "# and #" & Me.txtTo & "#"

If the controls always have values, you can use them as I suggested.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[misscrf]

I ended up solving all of this.

Here is how:

The activities table has a field for looking up the noforwardingreason in its lookup table, the updateaddress in its table and the nouspsupdate in its table.

I built a query that concantenated these fields lookup values, including the activity pk in this query.

Then on the main query counting the activities, I added this query to the design, it joined the activity table to it by the activity pk. Then I plopped in my concatenated field.

Voila. I grouped and counted that field and I'm set!

Thanks for all the help again.

I am sure the other solutions would have worked, but I can never figure something out, unless I can wrap my head around it.


misscrf

It is never too late to become what you could have been ~ George Eliot