Haywire Crosstab w/ forms and Reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having the strangest problem. I have created a report that is based
upon a crosstab query that has a date range parameter from a form. I had the
same problems that all the other people have had with the "parameter" issue.
I have corrected the "jet Engine" error but now when I open the report (in
design view) the parameter question pop up. Once I get through those by
selecting cancel the fields on my report have the little tag indicating that
the control sources are invalid. when I try to correct that the parameters
keep coming up until I finally have to end the session through the Task
manager.
 
Always get your parameters from values entered into controls on forms. Make
sure the form is open. Also, if possible, enter all the possible column
headings into the Column Headings property.
 
Appreciate the Reply Duane,

Yes the Startdate and Stopdate come form a form which launches a report
based upon the crosstab query. The report works like a charm when i take the
parameters out of the query and when in design view the "i" tag is gone from
the fields on the report. However, I need the user to be able to select a
date range.

Another strange thing is when I run the report with the parameters in the
query I get the "jet" error but it does not indicate which field is invalid,
it just gives me a quote mark " .
 
I don't know what the "i" tag is. You didn't specifically answer if the form
is open. Also, you didn't respond to my suggestion regarding the Column
Headings property.

Since we are talking about a crosstab, would you mind sharing your SQL view?

Does the crosstab work by itself?
 
Sorry, when I use parameters from the form and then open the report in design
view, the controls have a little green tiangle in the left corner. When I
click on the control a little "i" inside a diamond apears telling me that the
control is invalid.

SQL Follows:
PARAMETERS [Forms]![DateSelection]![Startdate] DateTime,
[Forms]![DateSelection]![Stopdate] DateTime;
TRANSFORM Count(Leads.CustomerID) AS CountOfCustomerID
SELECT Leads.[Source code], Count(Leads.CustomerID) AS [Total Of
CustomerID], Sum(Leads.[Sales Dollars]) AS [SumOfSales Dollars],
Sum(Leads.Commission) AS SumOfCommission
FROM Leads
WHERE (((Leads.[Contact date]) Between [Forms]![DateSelection]![Startdate]
And [Forms]![DateSelection]![Stopdate]))
GROUP BY Leads.[Source code], Leads.[Contact date],
Forms!DateSelection!Startdate, Forms!DateSelection!Stopdate
PIVOT Leads.[Lead Status];
 
Not sure why your SQL view isn't:
PARAMETERS [Forms]![DateSelection]![Startdate] DateTime,
[Forms]![DateSelection]![Stopdate] DateTime;
TRANSFORM Count(Leads.CustomerID) AS CountOfCustomerID
SELECT Leads.[Source code], Count(Leads.CustomerID) AS [Total Of
CustomerID], Sum(Leads.[Sales Dollars]) AS [SumOfSales Dollars],
Sum(Leads.Commission) AS SumOfCommission
FROM Leads
WHERE (((Leads.[Contact date]) Between [Forms]![DateSelection]![Startdate]
And [Forms]![DateSelection]![Stopdate]))
GROUP BY Leads.[Source code], Leads.[Contact date]
PIVOT Leads.[Lead Status];

Is the form open?
Are there legitimate date values in the controls?
Can you enter all possible values of [Lead Status] in the Column Headings
property of the crosstab query?

--
Duane Hookom
MS Access MVP
--

Dan S. said:
Sorry, when I use parameters from the form and then open the report in
design
view, the controls have a little green tiangle in the left corner. When I
click on the control a little "i" inside a diamond apears telling me that
the
control is invalid.

SQL Follows:
PARAMETERS [Forms]![DateSelection]![Startdate] DateTime,
[Forms]![DateSelection]![Stopdate] DateTime;
TRANSFORM Count(Leads.CustomerID) AS CountOfCustomerID
SELECT Leads.[Source code], Count(Leads.CustomerID) AS [Total Of
CustomerID], Sum(Leads.[Sales Dollars]) AS [SumOfSales Dollars],
Sum(Leads.Commission) AS SumOfCommission
FROM Leads
WHERE (((Leads.[Contact date]) Between [Forms]![DateSelection]![Startdate]
And [Forms]![DateSelection]![Stopdate]))
GROUP BY Leads.[Source code], Leads.[Contact date],
Forms!DateSelection!Startdate, Forms!DateSelection!Stopdate
PIVOT Leads.[Lead Status];



Duane Hookom said:
I don't know what the "i" tag is. You didn't specifically answer if the
form
is open. Also, you didn't respond to my suggestion regarding the Column
Headings property.

Since we are talking about a crosstab, would you mind sharing your SQL
view?

Does the crosstab work by itself?
Click to expand...
Click to expand...
 
You are the man,

I removed the Forms!DateSelection!Startdate, Forms!DateSelection!Stopdate
from the Group By and put all possible "lead Status' in the column heading
property. It is working like a charm. Final question, I assumed that the
column heading layout would be in the order that I entered the potential
column headings, i.e. "Inactive", "Prospect", "Client", "Sold".

Not complaining mind you :)

Thanks,.

Duane Hookom said:
Not sure why your SQL view isn't:
PARAMETERS [Forms]![DateSelection]![Startdate] DateTime,
[Forms]![DateSelection]![Stopdate] DateTime;
TRANSFORM Count(Leads.CustomerID) AS CountOfCustomerID
SELECT Leads.[Source code], Count(Leads.CustomerID) AS [Total Of
CustomerID], Sum(Leads.[Sales Dollars]) AS [SumOfSales Dollars],
Sum(Leads.Commission) AS SumOfCommission
FROM Leads
WHERE (((Leads.[Contact date]) Between [Forms]![DateSelection]![Startdate]
And [Forms]![DateSelection]![Stopdate]))
GROUP BY Leads.[Source code], Leads.[Contact date]
PIVOT Leads.[Lead Status];

Is the form open?
Are there legitimate date values in the controls?
Can you enter all possible values of [Lead Status] in the Column Headings
property of the crosstab query?

--
Duane Hookom
MS Access MVP
--

Dan S. said:
Sorry, when I use parameters from the form and then open the report in
design
view, the controls have a little green tiangle in the left corner. When I
click on the control a little "i" inside a diamond apears telling me that
the
control is invalid.

SQL Follows:
PARAMETERS [Forms]![DateSelection]![Startdate] DateTime,
[Forms]![DateSelection]![Stopdate] DateTime;
TRANSFORM Count(Leads.CustomerID) AS CountOfCustomerID
SELECT Leads.[Source code], Count(Leads.CustomerID) AS [Total Of
CustomerID], Sum(Leads.[Sales Dollars]) AS [SumOfSales Dollars],
Sum(Leads.Commission) AS SumOfCommission
FROM Leads
WHERE (((Leads.[Contact date]) Between [Forms]![DateSelection]![Startdate]
And [Forms]![DateSelection]![Stopdate]))
GROUP BY Leads.[Source code], Leads.[Contact date],
Forms!DateSelection!Startdate, Forms!DateSelection!Stopdate
PIVOT Leads.[Lead Status];



Duane Hookom said:
I don't know what the "i" tag is. You didn't specifically answer if the
form
is open. Also, you didn't respond to my suggestion regarding the Column
Headings property.

Since we are talking about a crosstab, would you mind sharing your SQL
view?

Does the crosstab work by itself?

--
Duane Hookom
MS Access MVP
--

Appreciate the Reply Duane,

Yes the Startdate and Stopdate come form a form which launches a report
based upon the crosstab query. The report works like a charm when i
take
the
parameters out of the query and when in design view the "i" tag is gone
from
the fields on the report. However, I need the user to be able to
select a
date range.

Another strange thing is when I run the report with the parameters in
the
query I get the "jet" error but it does not indicate which field is
invalid,
it just gives me a quote mark " .

:

Always get your parameters from values entered into controls on forms.
Make
sure the form is open. Also, if possible, enter all the possible
column
headings into the Column Headings property.

--
Duane Hookom
MS Access MVP
--

I am having the strangest problem. I have created a report that is
based
upon a crosstab query that has a date range parameter from a form.
I
had
the
same problems that all the other people have had with the
"parameter"
issue.
I have corrected the "jet Engine" error but now when I open the
report
(in
design view) the parameter question pop up. Once I get through
those
by
selecting cancel the fields on my report have the little tag
indicating
that
the control sources are invalid. when I try to correct that the
parameters
keep coming up until I finally have to end the session through the
Task
manager.
Click to expand...
Click to expand...
Click to expand...
 
The columns should appear in the order you entered them in the Column
Headings property.

--
Duane Hookom
MS Access MVP
--

Dan S. said:
You are the man,

I removed the Forms!DateSelection!Startdate, Forms!DateSelection!Stopdate
from the Group By and put all possible "lead Status' in the column heading
property. It is working like a charm. Final question, I assumed that the
column heading layout would be in the order that I entered the potential
column headings, i.e. "Inactive", "Prospect", "Client", "Sold".

Not complaining mind you :)

Thanks,.

Duane Hookom said:
Not sure why your SQL view isn't:
PARAMETERS [Forms]![DateSelection]![Startdate] DateTime,
[Forms]![DateSelection]![Stopdate] DateTime;
TRANSFORM Count(Leads.CustomerID) AS CountOfCustomerID
SELECT Leads.[Source code], Count(Leads.CustomerID) AS [Total Of
CustomerID], Sum(Leads.[Sales Dollars]) AS [SumOfSales Dollars],
Sum(Leads.Commission) AS SumOfCommission
FROM Leads
WHERE (((Leads.[Contact date]) Between
[Forms]![DateSelection]![Startdate]
And [Forms]![DateSelection]![Stopdate]))
GROUP BY Leads.[Source code], Leads.[Contact date]
PIVOT Leads.[Lead Status];

Is the form open?
Are there legitimate date values in the controls?
Can you enter all possible values of [Lead Status] in the Column Headings
property of the crosstab query?

--
Duane Hookom
MS Access MVP
--

Dan S. said:
Sorry, when I use parameters from the form and then open the report in
design
view, the controls have a little green tiangle in the left corner.
When I
click on the control a little "i" inside a diamond apears telling me
that
the
control is invalid.

SQL Follows:
PARAMETERS [Forms]![DateSelection]![Startdate] DateTime,
[Forms]![DateSelection]![Stopdate] DateTime;
TRANSFORM Count(Leads.CustomerID) AS CountOfCustomerID
SELECT Leads.[Source code], Count(Leads.CustomerID) AS [Total Of
CustomerID], Sum(Leads.[Sales Dollars]) AS [SumOfSales Dollars],
Sum(Leads.Commission) AS SumOfCommission
FROM Leads
WHERE (((Leads.[Contact date]) Between
[Forms]![DateSelection]![Startdate]
And [Forms]![DateSelection]![Stopdate]))
GROUP BY Leads.[Source code], Leads.[Contact date],
Forms!DateSelection!Startdate, Forms!DateSelection!Stopdate
PIVOT Leads.[Lead Status];



:

I don't know what the "i" tag is. You didn't specifically answer if
the
form
is open. Also, you didn't respond to my suggestion regarding the
Column
Headings property.

Since we are talking about a crosstab, would you mind sharing your SQL
view?

Does the crosstab work by itself?

--
Duane Hookom
MS Access MVP
--

Appreciate the Reply Duane,

Yes the Startdate and Stopdate come form a form which launches a
report
based upon the crosstab query. The report works like a charm when i
take
the
parameters out of the query and when in design view the "i" tag is
gone
from
the fields on the report. However, I need the user to be able to
select a
date range.

Another strange thing is when I run the report with the parameters
in
the
query I get the "jet" error but it does not indicate which field is
invalid,
it just gives me a quote mark " .

:

Always get your parameters from values entered into controls on
forms.
Make
sure the form is open. Also, if possible, enter all the possible
column
headings into the Column Headings property.

--
Duane Hookom
MS Access MVP
--

I am having the strangest problem. I have created a report that
is
based
upon a crosstab query that has a date range parameter from a
form.
I
had
the
same problems that all the other people have had with the
"parameter"
issue.
I have corrected the "jet Engine" error but now when I open the
report
(in
design view) the parameter question pop up. Once I get through
those
by
selecting cancel the fields on my report have the little tag
indicating
that
the control sources are invalid. when I try to correct that the
parameters
keep coming up until I finally have to end the session through
the
Task
manager.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top