Please help, How do I stop Parameter from Popping Up in Report Design View? (1 Viewer)

anonymer · Oct 8, 2014

I created a crosstab query with the dates as row headings which has a Start Date and End Date Parameter. When I created a report from it, everytime I switch to the Design View of the Report, the Parameter Pops-Up like 3 times so I had to either click Cancel Start Date and End Date Pop-Ups 3 times or Fill up the Parameter 3 times which is annoying. It pops up when I click something on the Report(in Design View), or even when I encode a Formula on a textbox(it pops up everytime I press a letter on my keyboard).

i can't remove the parameters from the query because I need it in producing the data for report (say from August 1 to 31 data only)

please help me solve this problem. thanks

JHB · Oct 8, 2014

Do you have a sample database you could post, (zip it because you haven't post 10 post yet), + name of the report in which you have the problem?

anonymer · Oct 8, 2014

My query is Apportionmentquery_datecrosstab and my Report is named Apportionmentquery_datecrosstab too.

I tried replacing the Source in the Apportionmentquery_crosstab Query to FSTransactions Table (but i don't think it was edited in this file) and create a crosstab report from it to see if it was the one causing the parameter to pop-up but still, the parameter pop-up didn't stop bugging me in design.

JHB · Oct 9, 2014

The problem is you are doing calculation on the parameters, and MS-Access doesn't like that!
I've made a solution where you input the dates in a form and then open the report.
Open the navigation form, choose "Dateform", input Start date = 08-28-2014 and End date= 09-28-2014, and click the button.

anonymer · Oct 10, 2014

Hi JHB, thanks for your reply. somehow the Parameter was still popping up everytime I click on the details columns. what solved the problem was adding "In (1,2,3,4,5);" at the PIVOT line in SQL of ApportionmentQuery_DateCrosstab Query.

The ApportionmentQuery_DateCrosstab Report can now be edited in design mode. My next problem is now how to comvert the Column Numbers Into Calculated Dates so that Column 1 will become 1st Sunday of the month (for example in an August 2014 Report, column 1 will be August 3, 2014. And For September, Column 1 will be September 7).

Thanks for the help and support JHB. I really appreciate it.

JHB · Oct 10, 2014

anonymer said:
... somehow the Parameter was still popping up everytime I click on the details columns. ...

Not by me in the database I've attached in thread #4.

anonymer said:
The ApportionmentQuery_DateCrosstab Report can now be edited in design mode. My next problem is now how to comvert the Column Numbers Into Calculated Dates so that Column 1 will become 1st Sunday of the month (for example in an August 2014 Report, column 1 will be August 3, 2014. And For September, Column 1 will be September 7).

Put the below code in a module:

Code:

Public Function NthXDay(N As Integer, d As Integer, dtD As Date) As Date
  NthXDay = Format(((7 - Weekday(DateSerial(Year(dtD), Month(dtD), 1)) + d) Mod 7 + 1 + (N - 1) * 7) & "-" & Month(dtD) & "-" & Year(dtD), "dd-mm-yyyy")
End Function

And create a query and put in the below SQL-String

Code:

SELECT NthXDay(1,1,[FSTransactionDate]) AS FirstSundayOfAMonth
FROM FSTransactions
WHERE (((FSTransactions.FSTransactionDate) Between [Forms]![Navigation Form]![NavigationSubform].[Form]![StartDate] And [Forms]![Navigation Form]![NavigationSubform].[Form]![EndDate]))
GROUP BY NthXDay(1,1,[FSTransactionDate])
ORDER BY NthXDay(1,1,[FSTransactionDate]);

Remember to put in some dates in the "Dateform" and then run the query.

Please help, How do I stop Parameter from Popping Up in Report Design View? (1 Viewer)

anonymer

New member

JHB

Have been here a while

anonymer

New member

Attachments

JHB

Have been here a while

Attachments

anonymer

New member

Attachments

JHB

Have been here a while

Similar threads

Users who are viewing this thread