The Microsoft jet database engine does not recognise…

[LB79]

Hello,

I'm having some problems with the “The Microsoft jet database engine does not recognise…” error message.
Looking on the net I gather its to do with referring to a control while using a crosstab. I've tried a couple of solutions but I'm still stuck (I even tried to set a text parameter).

This is my raw SQL – Id appreciate any advice on resolving this.

Thanks

SELECT MyTable1.ID, qryCrosstab.[Total]
FROM MyTable1 INNER JOIN qryCrosstab ON MyTable.[Item ID] = qryCrosstab.[Item ID]
WHERE (((MyTable1.Agent)=[Forms]![frm1]![subfrm].[Form]![cbo]));

 

[MSAccessRookie]

Hello,

I'm having some problems with the “The Microsoft jet database engine does not recognise…” error message.
Looking on the net I gather its to do with referring to a control while using a crosstab. I've tried a couple of solutions but I'm still stuck (I even tried to set a text parameter).

This is my raw SQL – Id appreciate any advice on resolving this.

Thanks

SELECT MyTable1.ID, qryCrosstab.[Total]
FROM MyTable1 INNER JOIN qryCrosstab ON MyTable.[Item ID] = qryCrosstab.[Item ID]
WHERE (((MyTable1.Agent)=[Forms]![frm1]![subfrm].[Form]![cbo]));

The above code looks like should work just fine, but it is only PART of the query that is having the problem. There is at least another part called qryCrosstab that may be the source of the problem, and If qryCrosstab calls additional Queries, they will need to be checked as well. Can we see the code for the other Queries?

 

[LB79]

Hi and thanks for looking.
I had previously altered the code a little (so not to give too much away), but for all the queries relating to this problem it would take me ages so I have posted the full code of them.

Thanks again for your advice

C20_qry2DrillDown
SELECT C20_qry1Target.Agent, C20_qry1Target.Category, C20_qry1Target.[Item ID], C20_qry1Target.FY, C20_qry1Target.CURR, C20_qry1Target.[Start Month], C20_qry1Target.[Total Target], C20_qry1Result.[Total Result], C20_qry1Result.[Variance Target] AS Variance, C20_qry1Target.[200904] AS T_200904, C20_qry1Result.[200904] AS R_200904, C20_qry1Target.[200905] AS T_200905, C20_qry1Result.[200905] AS R_200905, C20_qry1Target.[200906] AS T_200906, C20_qry1Result.[200906] AS R_200906, C20_qry1Target.[200907] AS T_200907, C20_qry1Result.[200907] AS R_200907, C20_qry1Target.[200908] AS T_200908, C20_qry1Result.[200908] AS R_200908, C20_qry1Target.[200909] AS T_200909, C20_qry1Result.[200909] AS R_200909, C20_qry1Target.[200910] AS T_200910, C20_qry1Result.[200910] AS R_200910, C20_qry1Target.[200911] AS T_200911, C20_qry1Result.[200911] AS R_200911, C20_qry1Target.[200912] AS T_200912, C20_qry1Result.[200912] AS R_200912, C20_qry1Target.[201001] AS T_201001, C20_qry1Result.[201001] AS R_201001, C20_qry1Target.[201002] AS T_201002, C20_qry1Result.[201002] AS R_201002, C20_qry1Target.[201003] AS T_201003, C20_qry1Result.[201003] AS R_201003, C20_qry1Target.Port, C20_qry1Target.Vendor, C20_qry1Target.Budgeted, C20_qry1Target.Description, C20_qry1Target.Status, C20_qry1Target.Control
FROM C20_qry1Target LEFT JOIN C20_qry1Result ON (C20_qry1Target.Agent=C20_qry1Result.Agent) AND (C20_qry1Target.[Item ID]=C20_qry1Result.[Item ID]) AND (C20_qry1Target.FY=C20_qry1Result.FY)
WHERE (((C20_qry1Target.Agent)=Forms!MNU_frm1_Menu!MNU_subfrm.Form!C20_cbo2));

C20_qry1Result
SELECT C20_tbl1aC200_Target_Details.Agent, C20_tbl1aC200_Target_Details.Category, C20_tbl1aC200_Target_Details.[Item ID], C20_tbl1aC200_Target_Details.FY, C20_tbl1aC200_Target_Details.CURR, C20_tbl1aC200_Target_Details.[Start Month], C20_qry0ResultCrosstab.[Total Result], [Total Result]-[Total Target] AS [Variance Target], C20_qry0ResultCrosstab.[200904], C20_qry0ResultCrosstab.[200905], C20_qry0ResultCrosstab.[200906], C20_qry0ResultCrosstab.[200907], C20_qry0ResultCrosstab.[200908], C20_qry0ResultCrosstab.[200909], C20_qry0ResultCrosstab.[200910], C20_qry0ResultCrosstab.[200911], C20_qry0ResultCrosstab.[200912], C20_qry0ResultCrosstab.[201001], C20_qry0ResultCrosstab.[201002], C20_qry0ResultCrosstab.[201003], C20_tbl1aC200_Target_Details.Port, C20_tbl1aC200_Target_Details.Vendor, C20_tbl1aC200_Target_Details.Budgeted, C20_tbl1aC200_Target_Details.Description, C20_tbl1aC200_Target_Details.Status, C20_tbl1aC200_Target_Details.Control
FROM (C20_tbl1aC200_Target_Details LEFT JOIN C20_qry0ResultCrosstab ON C20_tbl1aC200_Target_Details.[Item ID] = C20_qry0ResultCrosstab.[Item ID]) LEFT JOIN C20_qry1Target ON C20_tbl1aC200_Target_Details.[Item ID] = C20_qry1Target.[Item ID];

C20_qry1Target
SELECT C20_tbl1aC200_Target_Details.Agent, C20_tbl1aC200_Target_Details.Category, C20_tbl1aC200_Target_Details.[Item ID], C20_tbl1aC200_Target_Details.FY, C20_tbl1aC200_Target_Details.CURR, C20_tbl1aC200_Target_Details.[Start Month], C20_qry0TargetCrosstab.[Total Target], C20_qry0TargetCrosstab.[200904], C20_qry0TargetCrosstab.[200905], C20_qry0TargetCrosstab.[200906], C20_qry0TargetCrosstab.[200907], C20_qry0TargetCrosstab.[200908], C20_qry0TargetCrosstab.[200909], C20_qry0TargetCrosstab.[200910], C20_qry0TargetCrosstab.[200911], C20_qry0TargetCrosstab.[200912], C20_qry0TargetCrosstab.[201001], C20_qry0TargetCrosstab.[201002], C20_qry0TargetCrosstab.[201003], C20_tbl1aC200_Target_Details.Port, C20_tbl1aC200_Target_Details.Vendor, C20_tbl1aC200_Target_Details.Budgeted, C20_tbl1aC200_Target_Details.Description, C20_tbl1aC200_Target_Details.Status, C20_tbl1aC200_Target_Details.Control
FROM C20_tbl1aC200_Target_Details LEFT JOIN C20_qry0TargetCrosstab ON C20_tbl1aC200_Target_Details.[Item ID] = C20_qry0TargetCrosstab.[Item ID];

C20_qry0ResultCrosstab
TRANSFORM Sum(C20_tbl1C200_Result.Result) AS SumOfResult
SELECT C20_tbl1C200_Result.[Item ID], Sum(C20_tbl1C200_Result.Result) AS [Total Result]
FROM C20_tbl1C200_Result LEFT JOIN C20_tbl1bC200_Target ON (C20_tbl1C200_Result.MNTH = C20_tbl1bC200_Target.MNTH) AND (C20_tbl1C200_Result.[Item ID] = C20_tbl1bC200_Target.[Item ID])
GROUP BY C20_tbl1C200_Result.[Item ID]
PIVOT C20_tbl1bC200_Target.MNTH;

C20_qry0TargetCrosstab
TRANSFORM Sum(C20_tbl1bC200_Target.Target) AS SumOfTarget
SELECT C20_tbl1bC200_Target.[Item ID], Sum(C20_tbl1bC200_Target.Target) AS [Total Target]
FROM C20_tbl1bC200_Target
GROUP BY C20_tbl1bC200_Target.[Item ID]
PIVOT C20_tbl1bC200_Target.MNTH;