Enter StartDate and EndDate 9 times

[janfolmer]

I have a report, based on 2 queries. As you can see in the attachment I have ShopID header, and a report is mostly reporting for 8-9 shops. In that specific query I entered:
Between [StartDate] and [EndDate].

The annoying thing is, every time I create a report, it asks me for the StartDate, EndDate, RegionNumber, and with 9 shops in that report, that question for the start/end date keeps will pop up 9 times.

With 8 reports with each an average amount of 8 shops this becomes a cumbersome process. Is there a way to define the Enddate/StartDate for the whole report instead for each header that is reproduced in the report?

Can somebody point me in the rigth direction or refer me to online resources?
Thanks,...

 

[Smart]

I suggest you have a form with a start date field and an end date field and a region number field(combo box which contains a list of region numbers)

The user enters the start date and endate and chooses a region number from the combo box.
create a button and on the on click event call your report which is based on the query WWC_PoliciesForReport

In the above query in the date received field replace the criteria with
between Forms![formcontainingthestartendregionno fields].[startfieldname] and Forms![form containingthestartendregionnofields].[endfieldname]

in the region field replace the criteria with Forms![formcontainingthe startendregionnofields].[Regionfieldname]

 

[janfolmer]

Hey Smart,

Tried it, but I get a run time error from a code that I have to determine the bonuses in the report. A Run Time Error that I never had before, I previewed the report before. What triggers this Error, is it the from? I cannot attach the example file as this is to large (843 kb).

But what I did is the following: I created a form called "RunReport'. In this form 2 date fields (StartDate and EndDate) and a dropdown for the region.
I created a command button which is based on the query WWC_PoliciesForReport that previews the report.
In the above query in the date received field I replaced the criteria with between Forms![RunReports].[StartDate] and Forms![RunReports].[EndDate], and for the region field replaced the criteria with Forms![RunReports].[txtRegion].
When I click on the form I get a bug immediatelly. When I click on the report it asked me to put in the dates for the shops, and for the graphs (twice, see below why), and then I get the bug.

And then I have another little remark. The table is based on 1 query, namely WWC_PoliciesForReport, but also on a form. The graph at the end of the report is based on a form namely 'TEST'. This means I have to fill in twice the StartDate, EndDate and Region (one for all the shops and one for the graph) which is not a problem btw, but is there a chance to include this into the RunReport form?

 

[Psilokan]

I've encountered this issue many times. When you recieve this parameter box, its because it cant find the control you are specifying. That much I'm sure you could figure out on your own. The solution stumped me for a long time, because I was telling it exactly where the control was, but yet it wasnt seeing it.

Turns out, when you call the report it actually closes the form (why, I really dont know). So, to avoid this, right before it calls the report I told it to hide the form with a simple Me.Visible = False right before the DoCmd.OpenReport.

This solution solved my problem, however in my searches I found several solutions that worked for others, but not myself. Hopefully this will solve yours.

Best of luck

edit: This is assuming you are calling the report via a form as Smart suggested.

 

[janfolmer]

Hi Psilolkan,
Did that (your assumptions on calling the report via a form are correct), and it hides the form, but I still get the same bug:
"the 'Run-time Error '2427' You entered an expression that has no value"

I tried it with Bangs instead of periods and made sure the textbox names are spelled the same in the query as they are on the form.

This run time error does not ring a bell here. When I click on 'Debug' it opens the VBA editor and it highlights the following code:
Code:
Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer)
- > Select Case Policy
Case 1 To 4
Bonus = 0
Case 5 To 9
Bonus = 100
Case 10 To 14
Bonus = 200
End Select
End Sub

This particular code defines the projected bonus based on the number of 'policies sold' (1-4 policies sold,->no bonus, 5-9 -> bonus = 100). That code worked fine before, but now in combination with the form it is a bug.

I can email the zipfile, got your email address, just tell me whether you want to receive it.
Thanks.