Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Reports / Printing / March 2008

Tip: Looking for answers? Try searching our database.

Mysterious Parameter Added??

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Bumbino - 06 Mar 2008 16:07 GMT
I have a crosstab query that works just fine.  It has 2 Date/Time Query
parameters that I have declared in the "Query/Parameters" menu.  I have also
manually entered all the column headings in the properties of the query to
account for null values.  When I generate a report from this crosstab query,
the report adds another parameter and I cannot figure out why.  Here is the
layout of the crosstab query

                   Category1     Category2     CategoryX........
Auditor1         Total Hrs      Total Hrs       Total Hrs........
Auditor2         Total Hrs      Total Hrs       Total Hrs.......
etc
etc

When the report is run, it prompts me for the 2 date parameters but then
will also prompt for a 3rd parameter labeled  "Tablename.Category".  I just
hit Enter and do not enter any info into this parameter.  The end report
gives me what I want, but why the added parameter???  

Any help would be appreciated.
Reply to this Message
John Spencer - 06 Mar 2008 16:54 GMT
If you run the query (not the report) do you get the prompt?  Then the
problem is in the query.  The simplest was to fix this is to
-- open the query in SQL view and copy the SQL statement
-- open a new query
-- close the dialog for adding tables
-- select SQL view
-- paste the SQL into the new query
-- run the query and see if you get the prompt.  If not, save the new query
with the old query's name
(Oh, by the way, you might want to make a backup copy of the old query
before you do this)

If you only get the prompt when you use the report, then check
-- the sorting and grouping dialog for a reference to Tablename.Category
-- any conditional formatting for a reference to Tablename.Category
-- all your controls for a reference to Tablename.Category as their control
source

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I have a crosstab query that works just fine.  It has 2 Date/Time Query
> parameters that I have declared in the "Query/Parameters" menu.  I have
[quoted text clipped - 19 lines]
>
> Any help would be appreciated.
Reply to this Message
Bumbino - 06 Mar 2008 18:10 GMT
It only happens when I run the report.  Thanks for the ideas, I will check
them.

> If you run the query (not the report) do you get the prompt?  Then the
> problem is in the query.  The simplest was to fix this is to
[quoted text clipped - 37 lines]
> >
> > Any help would be appreciated.
Reply to this Message
Bumbino - 06 Mar 2008 19:13 GMT
I have checked all of your suggestions and nothing seems to be the problem.  
I tried to recreate the report from scratch and I still get the same issue.  
Here is the SQL statement from the query in case you can see anything that
might be the problem.  Thanks.

PARAMETERS [Enter Beginning Date] DateTime, [Enter Ending Date] DateTime;
TRANSFORM CDbl(Nz(Sum([Time Card].[Hours Worked]))) AS [SumOfHours Worked]
SELECT [Time Card].Auditor, Sum([Time Card].[Hours Worked]) AS [Total Of
Hours Worked]
FROM [Time Card]
WHERE ((([Time Card].[Week Ending]) Between [Enter Beginning Date] And
[Enter Ending Date]))
GROUP BY [Time Card].Auditor
PIVOT [Time Card].Category In ("Planning","Fieldwork","Exit
Conf/SOF","Workpaper Review","Final
Report/Wrap-Up","Travel","Follow-Up","PTO","Training","Meetings","Office
Admin/Time/Travel Exp","ACL Development","Other");

> If you run the query (not the report) do you get the prompt?  Then the
> problem is in the query.  The simplest was to fix this is to
[quoted text clipped - 37 lines]
> >
> > Any help would be appreciated.
Reply to this Message
John Spencer - 06 Mar 2008 19:58 GMT
Do you have a control bound to [Time Card].[Category] on the report?  If so,
you are not returning that value in the query.

Did you check the filter and sort properties of the report?

I don't see anything that would cause this problem.  Especially if you
really built the report from scratch.   You've got me scratching my head for
ideas.

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I have checked all of your suggestions and nothing seems to be the problem.
> I tried to recreate the report from scratch and I still get the same
[quoted text clipped - 61 lines]
>> >
>> > Any help would be appreciated.
Reply to this Message
Bumbino - 06 Mar 2008 20:43 GMT
Sorry but I used the wrong words in my reply.  I meant to say that I used the
Report wizard to recreate the report and got the same added parameter.  I
didn't mean to state that I recreated the report in design view.  Sorry about
that.  I have not added any new controls to the report.  I get the added
parameter popup as soon as the wizard has finished building the report based
off of the query.
Thanks for your help though!

> Do you have a control bound to [Time Card].[Category] on the report?  If so,
> you are not returning that value in the query.
[quoted text clipped - 70 lines]
> >> >
> >> > Any help would be appreciated.
Reply to this Message
Bumbino - 06 Mar 2008 20:53 GMT
Hey John, thanks for all the help.  I just got it working properly!

> Do you have a control bound to [Time Card].[Category] on the report?  If so,
> you are not returning that value in the query.
[quoted text clipped - 70 lines]
> >> >
> >> > Any help would be appreciated.
Reply to this Message
John Spencer - 07 Mar 2008 00:34 GMT
If you know what the problem was and the solution, would you care to
enlighten the rest of us.

It may come in handy for the next poor soul that runs into the problem.

'====================================================
 John Spencer
 Access MVP 2002-2005, 2007-2008
 Center for Health Program Development and Management
 University of Maryland Baltimore County
'====================================================

> Hey John, thanks for all the help.  I just got it working properly!
>
[quoted text clipped - 72 lines]
>>>>>
>>>>> Any help would be appreciated.
Reply to this Message
Bumbino - 07 Mar 2008 01:48 GMT
Of course, sorry about that.  In design view I brought the code up again and
scrolled down the alphabetized listing of all possible events.  I found an
event titled "OrderBy" with [Time Card].Category as its value.  I deleted the
value and the report works fine now.

Thanks again for all your help earlier.


> If you know what the problem was and the solution, would you care to
> enlighten the rest of us.
[quoted text clipped - 84 lines]
> >>>>>
> >>>>> Any help would be appreciated.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.