Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-07-2014, 08:15 PM   #1
anonymer
Newly Registered User
 
Join Date: Oct 2014
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
anonymer is on a distinguished road
Please help, How do I stop Parameter from Popping Up in Report Design View?

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

anonymer is offline   Reply With Quote
Old 10-07-2014, 10:48 PM   #2
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,081 Times in 2,036 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Please help, How do I stop Parameter from Popping Up in Report Design View?

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?
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-08-2014, 02:20 AM   #3
anonymer
Newly Registered User
 
Join Date: Oct 2014
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
anonymer is on a distinguished road
Re: Please help, How do I stop Parameter from Popping Up in Report Design View?

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.
Attached Files
File Type: zip GENERAL FUND DATABASE PROJECT- 2014.zip (1.94 MB, 57 views)

anonymer is offline   Reply With Quote
Old 10-08-2014, 11:11 PM   #4
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,081 Times in 2,036 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Please help, How do I stop Parameter from Popping Up in Report Design View?

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.
Attached Files
File Type: zip GENERAL FUND DATABASE PROJECT- 2014.zip (1.55 MB, 57 views)
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-09-2014, 07:19 PM   #5
anonymer
Newly Registered User
 
Join Date: Oct 2014
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
anonymer is on a distinguished road
Re: Please help, How do I stop Parameter from Popping Up in Report Design View?

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.
Attached Files
File Type: zip GENERAL FUND DATABASE PROJECT- 2014.zip (1.89 MB, 45 views)
anonymer is offline   Reply With Quote
Old 10-09-2014, 09:47 PM   #6
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,081 Times in 2,036 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Please help, How do I stop Parameter from Popping Up in Report Design View?

Quote:
Originally Posted by anonymer View Post
... 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.
Quote:
Originally Posted by anonymer View Post
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.

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Tags
parameters , queries , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop Window from Popping up Prayder Forms 3 09-09-2014 12:03 PM
Query parameter popping up sjr1917 Reports 5 01-31-2013 09:17 AM
Pop up won't stop popping up Johnny Drama Forms 1 04-05-2012 08:34 AM
Report prompts for parameter in design view pbuethe Reports 3 01-27-2009 08:27 AM
Parameter keeps popping up in design view Gkirkup Queries 1 06-07-2007 09:14 AM




All times are GMT -8. The time now is 05:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World