help with crosstabs 2

[drkhelmt]

This should be a simple deal but I can't seem to figure it out. Any help is appreciated.

I have 2 queries-

productionWork:

SELECT production.productionDate, employee.name, clProduction.hours
FROM production INNER JOIN (clProduction INNER JOIN employee ON clProduction.employeeID = employee.employeeID) ON production.productionID = clProduction.productionID
WHERE (((production.productionDate) Between [Forms]![getReport]![from_txt] And [Forms]![getReport]![to_txt]));

and
productionWork_crosstab:

TRANSFORM Sum(productionWork.hours) AS SumOfhours
SELECT Format([productionDate],"mmmm") AS [month], Sum(productionWork.hours) AS totalHours
FROM productionWork
GROUP BY Format([productionDate],"mmmm")
PIVOT productionWork.name;

When I run productionWork, all is well. No errors. When I run productionWork_crosstab I get "The Microsoft Jet database engine does not recognize '[Forms]![getReport]![from_txt]' as a valid field name or expression."

If I change production work to:

SELECT production.productionDate, employee.name, clProduction.hours
FROM production INNER JOIN (clProduction INNER JOIN employee ON clProduction.employeeID = employee.employeeID) ON production.productionID = clProduction.productionID
WHERE (((production.productionDate) Between #1/1/2006# And #1/18/2006#));

all runs well. How can I change it to use the dates I have on my form?

Any help is appreated. Thanks,


Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[HandsOnAccess]

The CrossTab will not work if the base query has "WHERE" condition which refers to a form value.

You can make the base query to Create a dummy table and use the dummy table in CrossTab Query.

SELECT production.productionDate, employee.name, clProduction.hours INTO Temp 
FROM production INNER JOIN (clProduction INNER JOIN employee ON clProduction.employeeID = employee.employeeID) ON production.productionID = clProduction.productionID
WHERE (((production.productionDate) Between [Forms]![getReport]![from_txt] And [Forms]![getReport]![to_txt]));

TRANSFORM Sum(Temp.hours) AS SumOfhours
SELECT Format([productionDate],"mmmm") AS [month], Sum(Temp.hours) AS totalHours
FROM Temp
GROUP BY Format([productionDate],"mmmm")
PIVOT Temp.name;

Regards,

 

[dhookom]

You can use a criteria from a form control or parameter in crosstabs. All you have to do is set the query parameter data type:
Query=>Parameters
[Forms]![getReport]![from_txt] Date/Time (or whatever)

You also generally don't have to enter parameter data types if you specify the Column Headings.

Creating a temporary table is a waste of resources.

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]

 

[HandsOnAccess]

I agree with dhookom,
Thanks dhookom, I realise my mistake too.
Drkhelmt - Follow dhookom's method, It is the correct way and more effecient than the method I suggested.

Regards,

 

[drkhelmt]

Thanks for the response.

It worked.... kinda.

Now I don't get any fields in the field list for the reports. I can run the queryies just fine and get the data needed, however, when I run the report, I get:

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

Here is the query behind the reports:

SELECT otherWork_crosstab.*, productionWork_crosstab.*
FROM otherWork_crosstab, productionWork_crosstab;

If I delete all bound text boxes on the report it runs, but with no data (of course).

Any thoughts?

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[drkhelmt]

Nevermind.... my mistake. I changed the querynames but did not change the data in the countrol sources.

I still don't get anything in the field list, but I guess that probably has to do with the crosstabs being dynamic in columns.

Thank you for the answers!

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[dhookom]

Consider entering all possible [Name] values into the Column Headings property.

You do realize that name is a poor name to give to a name property of any object since every object has a name 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]