Crosstab Queries with Between Data Parameters NOT working

[newbie]

I have two crosstab queries; Qry_SumCompliant and Qry_SumTotal. I have joined these two queries into a Select Query called Qry_FinalOutcome.



I would like to run the Qry_FinalOutcome with a specified date range. To do this I have created a form 'Frm_DateSelection' with two unbound date picker text boxes 'FromDate' and 'ThruDate' and a run button that runs Qry_FinalOutcome. I have declared my parameters on Qry_SumTotal [Forms]![Frm_DateSelection]![FromDate] / Date with Time and [Forms]![Frm_DateSelection]![ThruDate] / Date with Time. However, when I open my form, select my dates, and select run I get an error "The Microsoft Access database engine does not recognize " as a valid field name or expression". I have also tried declaring my parameters on both the crosstabs and on all three queries with the exact same result.


Qry_SumComliant

 
PARAMETERS [Forms]![Frm_DateSelection]![FromDate] DateTime, [Forms]![Frm_DateSelection]![ThruDate] DateTime;
TRANSFORM Sum(Tbl_30Day.CompliantCount) AS SumOfCompliant
SELECT Tbl_30Day.Contract, Tbl_30Day.DateRec, Sum(Tbl_30Day.CompliantCount) AS [Total Of Compliant]
FROM Tbl_30Day
GROUP BY Tbl_30Day.Contract, Tbl_30Day.DateRec
ORDER BY Tbl_30Day.DateRec
PIVOT Tbl_30Day.PY;

Qry_SumTotal

PARAMETERS [Forms]![Frm_DateSelection]![FromDate] DateTime, [Forms]![Frm_DateSelection]![ThruDate] DateTime;
TRANSFORM Sum(Tbl_30Day.TotalCount) AS SumOfTotal
SELECT Tbl_30Day.Contract, Tbl_30Day.DateRec, Sum(Tbl_30Day.TotalCount) AS [Total Of Total]
FROM Tbl_30Day
GROUP BY Tbl_30Day.Contract, Tbl_30Day.DateRec
ORDER BY Tbl_30Day.DateRec
PIVOT Tbl_30Day.PY;

Qry_FinalOutput

SELECT Qry_SumCompliant.Contract, Fix([P1]*10000)/10000 AS [Percent Compliant], Qry_SumTotal.DateRec, Qry_SumCompliant.[Total Of Compliant], Qry_SumTotal.[Total Of Total], [Total of Compliant]/[Total of Total] AS P1
FROM Qry_SumTotal INNER JOIN Qry_SumCompliant ON (Qry_SumTotal.Contract = Qry_SumCompliant.Contract) AND (Qry_SumTotal.DateRec = Qry_SumCompliant.DateRec)
GROUP BY Qry_SumCompliant.Contract, Fix([P1]*10000)/10000, Qry_SumTotal.DateRec, Qry_SumCompliant.[Total Of Compliant], Qry_SumTotal.[Total Of Total], [Total of Compliant]/[Total of Total]
ORDER BY Qry_SumCompliant.Contract, Qry_SumTotal.DateRec;

[June7]

Parameters are declared but not seeing a WHERE clause using them. However, don't think that is cause for the error.

[newbie]

Sorry, I forgot to add that I have tried with and without a WHERE clause.

[June7]

Don't see cause for failure. If you want to provide db for analysis, follow instructions at bottom of my post.

[newbie]

I figured it out! I took all my parameters referring to any date ranges out and ran my Qry_FinalOutput. When I did that I got a much clearer message about my field labeled P1. Once I fixed that field I declared my parameters on the Qry_FinalOutput and it worked like a charm.

[June7]

P1 should be PY in the Fix() function? Dang, I should have seen that.