Crosstab query problem

[Harley Feldman]

I have a crosstab query, qryInventorCodes_Crosstab, based on another query, qryInventorCodes. The one criteria field in qryInventorCodes is "BETWEEN [Forms]![Disclosures Report Date Range]![BeginDate] AND [Forms]![Disclosures Report Date Range]![EndDate]" based on the popup form [Disclosures Report Date Range]. I have declared both of these date fields in the Query Parameters as date types.

I can run the crosstab query by calling it directly with no problems. However, when I make it the source for a report, I get the following message:

"The Microsoft Jet database engine does not recognize " as a valid field name or expression."

I assumed that setting the Query Parameters would have solved this problem. Any ideas as to why this is not working?

Harley

 

[Guest]

Forms and reports expect a field to be there when you create them. One way to
fix this problem is to open the crosstab query in design view; right click in
the area near the tables; and select Properties. Next go into the Column
Headings and put in something like:

'19-NOV-06','20-NOV-06','21-NOV-06','22-NOV-06'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And for your needs, it will
create an empty field for the form or report even if there is no matching
data.

Of course this only works if you always know what your column heading need
to be. If not, there are ways to dynamically create the column headings and
controls on the form/report. Ask again if you need that solution.

 

[Harley Feldman]

Jerry,

I created the column headings property as you suggested as follows:

"LastName","FirstName","Total Of DisclosureID",
"SubmissionDays","CountSubmissionDays","Approved","Rejected","ProjectApproved","ProjectRejected","0","1","2","3","4"

Now I get the following message:

"The expression is typed incorrectly, or it is too complex to be
evaluated."

Am I doing the column properties incorrectly?

Harley

 

[Guest]

Hi,

Please post the entire SQL statement and a sample of the output. I'll see
what I can do.

 

[Harley Feldman]

Jerry,

Here is the original query with the BeginDate and EndDate parameters:

PARAMETERS [Forms]![Disclosures Report Date Range]![BeginDate] DateTime,
[Forms]![Disclosures Report Date Range]![EndDate] DateTime;
SELECT InventionDisclosures.DisclosureID, Inventors.InventorLName,
Inventors.InventorFName, InventionDisclosures.Code,
InventionDisclosures.FilingDate AS [Filing Date],
IIf(IsNull([SubmissionDate]) And Not
IsNull([ConceptionDate]),Date()-[ConceptionDate],[SubmissionDate]-[ConceptionDate])
AS SubmissionDays, IIf(Not IsNull([SubmissionDate]) And Not
IsNull([ConceptionDate]),1,0) AS CountSubmissionDays, IIf(Not
IsNull([ApprovalDate]),1,0) AS Approved, IIf(Not
IsNull([RejectionDate]),1,0) AS Rejected, IIf(Not IsNull([ApprovalDate]) And
Not IsNull([ProjectRelated]),1,0) AS ProjectApproved, IIf(Not
IsNull([RejectionDate] And Not IsNull([ProjectRelated])),1,0) AS
ProjectRejected, DisclosureInventors.PrimaryOrSecondary,
InventionDisclosures.FilingDate
FROM InventionDisclosures INNER JOIN (Inventors INNER JOIN
DisclosureInventors ON Inventors.InventorID =
DisclosureInventors.InventorID) ON InventionDisclosures.DisclosureID =
DisclosureInventors.DisclosureID
GROUP BY InventionDisclosures.DisclosureID, Inventors.InventorLName,
Inventors.InventorFName, InventionDisclosures.Code,
InventionDisclosures.FilingDate, IIf(IsNull([SubmissionDate]) And Not
IsNull([ConceptionDate]),Date()-[ConceptionDate],[SubmissionDate]-[ConceptionDate]),
IIf(Not IsNull([SubmissionDate]) And Not IsNull([ConceptionDate]),1,0),
IIf(Not IsNull([ApprovalDate]),1,0), IIf(Not IsNull([RejectionDate]),1,0),
IIf(Not IsNull([ApprovalDate]) And Not IsNull([ProjectRelated]),1,0),
IIf(Not IsNull([RejectionDate] And Not IsNull([ProjectRelated])),1,0),
DisclosureInventors.PrimaryOrSecondary, InventionDisclosures.FilingDate
HAVING (((InventionDisclosures.FilingDate) Between [Forms]![Disclosures
Report Date Range]![BeginDate] And [Forms]![Disclosures Report Date
Range]![EndDate]) AND ((DisclosureInventors.PrimaryOrSecondary)="Primary"));

And a sample of the output:

qryInventorCodes DisclosureID InventorLName InventorFName Code Filing Date
SubmissionDays CountSubmissionDays Approved Rejected ProjectApproved
ProjectRejected FilingDate
48 Walker Joe 4 9/1/2006 14 1 1 0 1 0 9/1/2006
66 Alvarez Guy 4 3/31/2006 44 1 1 0 1 0 3/31/2006
94 Salo Rodney 4 12/22/2005
0 1 0 1 0 12/22/2005
96 Salo Rodney 4 12/22/2005 70 1 1 0 1 0 12/22/2005
97 Salo Rodney 4 10/14/2005 6 1 1 0 1 0 10/14/2005


Here is the crosstab query based on the above:

TRANSFORM Count(qryInventorCodes.DisclosureID) AS CountOfDisclosureID
SELECT qryInventorCodes.InventorLName AS LastName,
qryInventorCodes.InventorFName AS FirstName,
Count(qryInventorCodes.DisclosureID) AS [Total Of DisclosureID],
Sum(qryInventorCodes.SubmissionDays) AS SubmissionDays,
Sum(qryInventorCodes.CountSubmissionDays) AS CountSubmissionDays,
Sum(qryInventorCodes.Approved) AS Approved, Sum(qryInventorCodes.Rejected)
AS Rejected, Sum(qryInventorCodes.ProjectApproved) AS ProjectApproved,
Sum(qryInventorCodes.ProjectRejected) AS ProjectRejected
FROM qryInventorCodes
WHERE (((qryInventorCodes.PrimaryOrSecondary)="Primary") AND
((qryInventorCodes.Code)<>5))
GROUP BY qryInventorCodes.InventorLName, qryInventorCodes.InventorFName,
qryInventorCodes.PrimaryOrSecondary
PIVOT qryInventorCodes.Code;

Here is what the crosstab should look like:

qryInventorCodes_Crosstab LastName FirstName Total Of DisclosureID
SubmissionDays CountSubmissionDays Approved Rejected ProjectApproved
ProjectRejected 4
Alvarez Guy 1 44 1 1 0 1 0 1
Salo Rodney 3 76 2 3 0 3 0 3
Walker Joe 1 14 1 1 0 1 0 1


Thanks for taking a look,

Harley