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!

 

[PHV]

Add the PARAMETERS instruction to PayrollFeederQuery too ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

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

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)) AND ProjectTypeAbbrev is Not Null;

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]

I tried adding the parameters code to the feeder query, but I still got the error message. I also checked the ProjectTypeAbbrev values and all are complete, no nulls. Any other ideas?

 

[PHV]

I'd suppress the blank line after the PARAMETERS instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kimsMTC]

There really isn't a blank line in the code. I only put it in for readability in this posting.

Kim

 

[dhookom]

I hate the report filtering solution that opens a form from the On Open event of the report. Have you tried opening the form first and setting the criteria values and then opening the query?

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]

Since it is hte report I really want, are you saying that I should open the PayrollPrompt form, enter my date, and when I click the PREVIEW button, write code to launch the report and pass the parameters to it? This is what I tried initially but I couldn't find code to do it. All posts recommended the ON OPEN approach. Can you help me with the code?

Thanks!

Kim

 

[dhookom]

You simply open your form first. Then you open the report. You can use a command button on the form that opens the report.

All I want you to do first is just to try what I suggested and report back your results.

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]

OK, I changed the form to launch the feederquery. It worked fine. I changed the form to launch the crosstab query. It worked fine. I'm now working on code to launch the report from the button, but am having trouble getting the WHERE condition like it wants it.

 

[kimsMTC]

I cannot add a WHERE clause because it's a crosstab and Access tries to add the WHERE clause in the wrong place. If I just do an OpenReport and not pass anything and leave the feeder query to grab the values from the PayrollPrompt window, then I'm back to the original error message. HELP PLEASE!!

Kim

 

[dhookom]

The "feeder" query you provided the SQL view for does have the criteria in it. The form I have been asking you to open is "PayrollPrompt".

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]

That is the form I have been making the changes to. I'm not sure what else you are asking me to do.

 

[dhookom]

Where are you attempting to "add a WHERE clause"? I don't expect you to use the WHERE CONDITION of the DoCmd.OpenReport method.

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]

I have changed so many things that I'm getting confused! This morning I tried this...I removed the WHERE clause from the feeder query and added it to the CROSSTAB QUERY. However, I still get the Jet error message about not recognizing " as a field or expression.

Yes, I did try the WHERE clause in the DoCmd.OpenReport method yesterday and that was the one that tries to put the WHERE clause at the end.

I found another blog that says this message is caused because the query must also have an alias for the parameter, but I cannot find anything to tell me how and where to create the alias and then where do I reference the alias name? Do this make sense to you?

Thanks!

 

[dhookom]

Can you provide the SQL view of both queries? Also, do the queries open on their own? Is the problem only created when the report is opened?

What is the exact error message?

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]

Like I said before, I've changed alot of things, but as of now, I'm back to my original design which means the problem is only when I open the report. The report opens the prompt window, I enter an end date, the prompt window calculates the beginning date and stores it in an invisible field on the form, then the queries run and I get the following error message:

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

Here's the feeder query code:

SELECT Department.DepartmentName, ProjectType.ProjectTypeAbbrev, Time.WorkDate, Time.Hours, Employee.LastName+", "+Employee.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));


Here's the crosstab query 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;

Thanks for sticking with me on this. I really need an answer!

Kim

 

[dhookom]

Open the form PayrollPrompt and enter appropriate date(s). Then open the crosstab query to see if it displays as expected. Check all the column headings to see if you have one named "<>".

I expect your ProjectTypeAbbrev are standard values that won't change much. Consider viewing the crosstab query properties and entering all possible ProjectTypeAbbrev values into the Column Headings property.

If the crosstab opens without issue (and looks reasonable), then the problem is with your report. Check the sorting and grouping as well as control sources.

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]

This is so frustrating. I'm not finding anything wrong. All the Column Headings are correct. The crosstab query opens and displays with no problem. My sorting and grouping are on DepartmentName and FullName. Now, the only thing is that FullName is a concatenated field in the feeder query. Could that be causing the problem?

 

[dhookom]

The concatenation should not be causing any issues however you should use [green][LastName] & ", " & [FirstName] AS FullName[/green] as I suggested in my sample sql a while back.

How are you binding text box control sources when the report is based on a crosstab query that might have dynamic column headings? Did you try my suggestion

entering all possible ProjectTypeAbbrev values into the Column Headings property

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]