Crosstab Nested Query Parameter problem

[kimsMTC]

I had the same problem referenced in thread701-953137. I added the Parameters code to my crosstab query SQL code. I still get the same JET error message but this time it says "The Microsoft Jet Database engine does not recognize " as a valid field name or expression. I cannot determine where it is reading a double-quote or perhaps it's blank. Here's my scenario:

I have a report called PayrollCrosstabReport. In the OPEN event I call a form, PayrollPrompt. On the PayrollPrompt form I ask the user to enter the Pay Period Ending Date (called DtPeriodEnding). I calculate in the AFTERUPDATE event the DtPeriodStarting by subtracting 14 days from what the user entered.

The PayrollCrosstabReport is based on a crosstab query called PayrollCrosstabQuery. The PayrollCrosstabQuery is based on PayrollFeederQuery.

Here's the PayrollCrosstabQuery code:

PARAMETERS [Forms]![PayrollPrompt]![DtPeriodStarting] DateTime, [Forms]![PayrollPrompt]![DtPeriodEnding] DateTime;

TRANSFORM Sum(PayrollFeederQuery.Hours) AS SumOfHours
SELECT PayrollFeederQuery.DepartmentName, PayrollFeederQuery.EmployeeNumber, PayrollFeederQuery.FullName, Sum(PayrollFeederQuery.Hours) AS [Total Of Hours]
FROM PayrollFeederQuery
GROUP BY PayrollFeederQuery.DepartmentName, PayrollFeederQuery.EmployeeNumber, PayrollFeederQuery.FullName
PIVOT PayrollFeederQuery.ProjectTypeAbbrev;


Here's my code for PayrollFeederQuery:

SELECT Department.DepartmentName, ProjectType.ProjectTypeAbbrev, Time.WorkDate, Time.Hours, [LastName]+", "+[FirstName] AS FullName, Employee.EmployeeNumber
FROM ProjectType INNER JOIN (Department INNER JOIN (Employee INNER JOIN (Project INNER JOIN [Time] ON Project.ProjectID=Time.ProjectID) ON Employee.EmployeeID=Time.EmployeeID) ON Department.DepartmentID=Employee.DepartmentID) ON ProjectType.ProjectTypeID=Project.ProjectTypeID
WHERE (((Time.WorkDate) Between Forms!PayrollPrompt!DtPeriodStarting And Forms!PayrollPrompt!DtPeriodEnding));


Please help!

Thank you!

 

[kimsMTC]

Finally I am getting somewhere!! I went back and re-read all your suggestions. I realized what you were asking me to a few postings back was to hardcode the column headings. So now I do not get the ugly Jet message! Yeah! However, now I get prompted to enter a value in any of the column headings where no data exists. How do I get around that?

Thank you so, so much!

Kim

 

[kimsMTC]

OK, I just wanted to post what the final solution was! Thank you dhookom for all your effort and solution! The entire problem was that I didn't have all the possible column headings for the crosstab identified in the crosstab query. Once I entered them in the Properties/Column Headings window (enter them in double-quotes, separated by commas), then I added a new text field that summed the values for each of the possible Column Headings, everything worked beautifully!

Thanks again!

Kim

 

[dhookom]

Your solution seems to suggest my first posting might have been been your issue

What about making sure there are no Null values in the ProjectTypeAbbrev field

I'm glad to hear you have found success.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kimsMTC]

No, I added the NULL values when you first suggested, but that did not stop the issue. The NULL values was when each row had a different set of column needs. Anyway, thanks again.

Kim