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
Code:
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
Code:
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
Code:
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;