Mysterious Parameter Added??

[Bumbino]

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.

 

[John Spencer]

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

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

 

[Bumbino]

It only happens when I run the report. Thanks for the ideas, I will check
them.

 

[Bumbino]

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");

 

[John Spencer]

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.

--
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
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
-- 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

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

 

[Bumbino]

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.

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.

--
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
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
-- 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

--
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
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.

 

[Bumbino]

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.

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.

--
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
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
-- 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

--
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
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.

 

[John Spencer]

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!

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.

--
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
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
-- 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

--
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
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.

 

[Bumbino]

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.

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!

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.

--
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
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
-- 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

--
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
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.