Jet Database does not recognize...

[dRahme]

Hello, I am trying to troubleshoot a query and it involves a cross-tab and I have little experience with cross-tabs

The query I am trying to run is qryDrawingCurveActual :

SELECT DrawingCurveActualTable.Date, IIf(IsNull([qryDrawingCurveWeeklyActual_Crosstab]![Civil]),0,[qryDrawingCurveWeeklyActual_Crosstab]![Civil]) AS [Actual - Civil], IIf(IsNull([qryDrawingCurveWeeklyActual_Crosstab]![Electrical]),0,[qryDrawingCurveWeeklyActual_Crosstab]![Electrical]) AS [Actual - Electrical], IIf(IsNull([qryDrawingCurveWeeklyActual_Crosstab]![Electrical Interconnects and Schematics]),0,[qryDrawingCurveWeeklyActual_Crosstab]![Electrical Interconnects and Schematics]) AS [Actual - Electrical Interconnects and Schematics], IIf(IsNull([qryDrawingCurveWeeklyActual_Crosstab]![Mechanical]),0,[qryDrawingCurveWeeklyActual_Crosstab]![Mechanical]) AS [Actual - Mechanical], IIf(IsNull([qryDrawingCurveWeeklyActual_Crosstab]![Mechanical ISO's]),0,[qryDrawingCurveWeeklyActual_Crosstab]![Mechanical ISO's]) AS [Actual - Mechanical ISO's], IIf(IsNull([qryDrawingCurveWeeklyActual_Crosstab]![Mechanical P&ID's]),0,[qryDrawingCurveWeeklyActual_Crosstab]![Mechanical P&ID's]) AS [Actual - Mechanical P&ID's], IIf(IsNull([qryDrawingCurveWeeklyActual_Crosstab]![Structural]),0,[qryDrawingCurveWeeklyActual_Crosstab]![Structural]) AS [Actual - Structural]
FROM DrawingCurveActualTable LEFT JOIN qryDrawingCurveWeeklyActual_Crosstab ON DrawingCurveActualTable.Date = qryDrawingCurveWeeklyActual_Crosstab.CurrentMondayWeek;

When I run this, it gives me a 'Microsoft Jet Db does not recognize the query.

This query is composed of a table and a cross-tab query where they are joined on a date.

The crosstab sql is:

PARAMETERS [ [Forms]]![dlgFilterDrwgCurve]![CurveDataType] Text ( 255 );
TRANSFORM Sum(qryDrawingCurveWeekly.TotalIssues) AS SumOfTotalIssues
SELECT qryDrawingCurveWeekly.CurrentMondayWeek, Sum(qryDrawingCurveWeekly.TotalIssues) AS [Total Of TotalIssues]
FROM qryDrawingCurveWeekly
WHERE (((qryDrawingCurveWeekly.Issue) Like 'Actual'))
GROUP BY qryDrawingCurveWeekly.CurrentMondayWeek, qryDrawingCurveWeekly.Issue
PIVOT qryDrawingCurveWeekly.drwg_grp_desc;

The cross tab itself works as it should (at least no errors).

I suspect based on some other posts I read that this has something to do with the date field in the crosstab, but the query is not looking for a date parameter based on a form.

Any thoughts would be appreciated.

Thank, dRahme

 

[dRahme]

Never Mind, the problem was not in the query per se. It was looking for a couple fields that did not exist.