mismatch in expression type error in a massive access query

[Snorky85]

Hi wondering if anyone can help me unpick where this mismatch is occuring?

I am trying to unpick an access database that someone else has built :banghead:

The SQL for the qry is:

SELECT 2014 AS FinancialYr, qry_Union_ExportW1.CostCentre, qry_Union_ExportW1.Subjective, qry_Union_ExportW1.SA1, qry_Union_ExportW1.SA2, qry_Union_ExportW1.Employee_Name, qry_Union_ExportW1.Assignment_No, qry_Union_ExportW1.Element_Code, qry_Union_ExportW1.PCT_Code, qry_Union_ExportW1.PCT_Description, qry_Union_ExportW1.Income_Category, qry_Union_ExportW1.Comment, qry_Union_ExportW1.Model_ID, qry_Union_ExportW1.Source, qry_Union_ExportW1.Data_Source, qry_Union_ExportW1.Phasing_ID, Sum(qry_Union_ExportW1.Planning_Outturn) AS Planning_Outturn, Sum(qry_Union_ExportW1.Actual_WTE) AS Actual_WTE, Sum(IIf([Period_12]=0,0,[qry_Union_ExportW1].[Actual_WTE])) AS Closing_WTE, Sum(qry_Union_ExportW1.Annual_Budget) AS Annual_Budget, Sum(qry_Union_ExportW1.Funded_WTE) AS Funded_WTE, Sum(qry_Union_ExportW1.ForecastOutturn) AS ForecastOutturn, Sum(qry_Union_ExportW1.PrRata) AS M12RunRate, tDef_GL_Subjective.[Subjective Name] AS [Subj Description], Switch([bCat_1] Is Not Null,[bCat_1],[aCat_1] Is Not Null,[aCat_1],True,[Cat_1]) AS Cat1, Switch([bCat_2] Is Not Null,[bCat_2],[aCat_2] Is Not Null,[aCat_2],True,[Cat_2]) AS Cat2, Switch([bCat_3] Is Not Null,[bCat_3],[aCat_3] Is Not Null,[aCat_3],True,[Cat_3]) AS Cat3, Switch([bPayCat_1] Is Not Null,[bPayCat_1],[aPayCat_1] Is Not Null,[aPayCat_1],True,[PayCat_1]) AS [Pay Cat 1], Switch([bPayCat_2] Is Not Null,[bPayCat_2],[aPayCat_2] Is Not Null,[aPayCat_2],True,[PayCat_2]) AS [Pay Cat 2], Switch([bPayCat_3] Is Not Null,[bPayCat_3],[aPayCat_3] Is Not Null,[aPayCat_3],True,[PayCat_3]) AS [Pay Cat 3], Switch([bLTFM] Is Not Null,[bLTFM],[aLTFM] Is Not Null,[aLTFM],True,[tdef_GL_Subjective].[LTFM]) AS LTFM, Switch([bLTFM2] Is Not Null,[bLTFM2],[aLTFM2] Is Not Null,[aLTFM2],True,[tdef_GL_Subjective].[LTFM2]) AS LTFM2, Switch([bEBITDA] Is Not Null,[bEBITDA],[aEBITDA] Is Not Null,[aEBITDA],True,[tdef_GL_Subjective].[EBITDA]) AS EBITDA, tDef_GL_SA1.[SA1 Description], tDef_GL_SA2.[SA2 Description], tDef_GL_Level3.[L3 Name] AS [Level 3], tDef_GL_Level4.[L4 Name] AS [Level 4], tDef_GL_Level5.[L5 Name] AS [Level 5], tDef_GL_Level4.[Lead Accountant] AS Accountant, tDef_GL_CostCentres.[Cost Centre Name] AS [SBS Desc], Switch([bMonitor] Is Not Null,[bMonitor],[aMonitor] Is Not Null,[aMonitor],True,[tDef_GL_Subjective].[Monitor]) AS Monitor, Switch([bMonitor_Sub] Is Not Null,[bMonitor_Sub],[aMonitor_Sub] Is Not Null,[aMonitor_Sub],True,[Monitor_Sub]) AS [Monitor (Sub)], Switch([bMonitor_Sub2] Is Not Null,[bMonitor_Sub2],[aMonitor_Sub2] Is Not Null,[aMonitor_Sub2],True,[Monitor_Sub2]) AS [Monitor (Sub2)], Switch([bMonitor_Sub3] Is Not Null,[bMonitor_Sub3],[aMonitor_Sub3] Is Not Null,[aMonitor_Sub3],True,[Monitor_Sub3]) AS [Monitor (Sub3)], Switch([bMonitor_Workforce1] Is Not Null,[bMonitor_Workforce1],[aMonitor_Workforce1] Is Not Null,[aMonitor_Workforce1],True,[Monitor_Workforce1]) AS [Monitor (Workforce 1)], Switch([bMonitor_Workforce2] Is Not Null,[bMonitor_Workforce2],[aMonitor_Workforce2] Is Not Null,[aMonitor_Workforce2],True,[Monitor_Workforce2]) AS [Monitor (Workforce 2)], tDef_GL_CostCentres.[Cost Centre Type] AS [Level 6], IIf(([Source] Like "CIP*") And ([Source] Not Like "*_RT"),IIf(InStr(1,[Comment],":")=0,Null,Left([Comment],InStr(1,[Comment],":")-1)),Null) AS CIPSchemeNo
FROM tDef_GL_SA1 RIGHT JOIN (((tDef_GL_Level3 RIGHT JOIN tDef_GL_Level4 ON tDef_GL_Level3.L3_Code = tDef_GL_Level4.L3_Code) RIGHT JOIN tDef_GL_Level5 ON tDef_GL_Level4.L4_Code = tDef_GL_Level5.L4_Code) RIGHT JOIN (tDef_GL_SA2 RIGHT JOIN ((tDef_GL_Subjective_bCCSubj_Override RIGHT JOIN (tDef_GL_Subjective_aSubjSA1_Override RIGHT JOIN (tDef_GL_Subjective RIGHT JOIN (tbl_PhasingID RIGHT JOIN qry_Union_ExportW1 ON tbl_PhasingID.Phasing_ID = qry_Union_ExportW1.Phasing_ID) ON tDef_GL_Subjective.Subjective_Code = qry_Union_ExportW1.Subjective) ON (tDef_GL_Subjective_aSubjSA1_Override.SA1 = qry_Union_ExportW1.SA1) AND (tDef_GL_Subjective_aSubjSA1_Override.Subjective_Code = qry_Union_ExportW1.Subjective)) ON (tDef_GL_Subjective_bCCSubj_Override.Subjective_Code = qry_Union_ExportW1.Subjective) AND (tDef_GL_Subjective_bCCSubj_Override.CostCentre = qry_Union_ExportW1.CostCentre)) LEFT JOIN tDef_GL_CostCentres ON qry_Union_ExportW1.CostCentre = tDef_GL_CostCentres.CostCentre) ON tDef_GL_SA2.SA2 = qry_Union_ExportW1.SA2) ON tDef_GL_Level5.L5_Code = tDef_GL_CostCentres.L5_Code) ON tDef_GL_SA1.SA1_Code = qry_Union_ExportW1.SA1
GROUP BY 2014, qry_Union_ExportW1.CostCentre, qry_Union_ExportW1.Subjective, qry_Union_ExportW1.SA1, qry_Union_ExportW1.SA2, qry_Union_ExportW1.Employee_Name, qry_Union_ExportW1.Assignment_No, qry_Union_ExportW1.Element_Code, qry_Union_ExportW1.PCT_Code, qry_Union_ExportW1.PCT_Description, qry_Union_ExportW1.Income_Category, qry_Union_ExportW1.Comment, qry_Union_ExportW1.Model_ID, qry_Union_ExportW1.Source, qry_Union_ExportW1.Data_Source, qry_Union_ExportW1.Phasing_ID, tDef_GL_Subjective.[Subjective Name], Switch([bCat_1] Is Not Null,[bCat_1],[aCat_1] Is Not Null,[aCat_1],True,[Cat_1]), Switch([bCat_2] Is Not Null,[bCat_2],[aCat_2] Is Not Null,[aCat_2],True,[Cat_2]), Switch([bCat_3] Is Not Null,[bCat_3],[aCat_3] Is Not Null,[aCat_3],True,[Cat_3]), Switch([bPayCat_1] Is Not Null,[bPayCat_1],[aPayCat_1] Is Not Null,[aPayCat_1],True,[PayCat_1]), Switch([bPayCat_2] Is Not Null,[bPayCat_2],[aPayCat_2] Is Not Null,[aPayCat_2],True,[PayCat_2]), Switch([bPayCat_3] Is Not Null,[bPayCat_3],[aPayCat_3] Is Not Null,[aPayCat_3],True,[PayCat_3]), Switch([bLTFM] Is Not Null,[bLTFM],[aLTFM] Is Not Null,[aLTFM],True,[tdef_GL_Subjective].[LTFM]), Switch([bLTFM2] Is Not Null,[bLTFM2],[aLTFM2] Is Not Null,[aLTFM2],True,[tdef_GL_Subjective].[LTFM2]), Switch([bEBITDA] Is Not Null,[bEBITDA],[aEBITDA] Is Not Null,[aEBITDA],True,[tdef_GL_Subjective].[EBITDA]), tDef_GL_SA1.[SA1 Description], tDef_GL_SA2.[SA2 Description], tDef_GL_Level3.[L3 Name], tDef_GL_Level4.[L4 Name], tDef_GL_Level5.[L5 Name], tDef_GL_Level4.[Lead Accountant], tDef_GL_CostCentres.[Cost Centre Name], Switch([bMonitor] Is Not Null,[bMonitor],[aMonitor] Is Not Null,[aMonitor],True,[tDef_GL_Subjective].[Monitor]), Switch([bMonitor_Sub] Is Not Null,[bMonitor_Sub],[aMonitor_Sub] Is Not Null,[aMonitor_Sub],True,[Monitor_Sub]), Switch([bMonitor_Sub2] Is Not Null,[bMonitor_Sub2],[aMonitor_Sub2] Is Not Null,[aMonitor_Sub2],True,[Monitor_Sub2]), Switch([bMonitor_Sub3] Is Not Null,[bMonitor_Sub3],[aMonitor_Sub3] Is Not Null,[aMonitor_Sub3],True,[Monitor_Sub3]), Switch([bMonitor_Workforce1] Is Not Null,[bMonitor_Workforce1],[aMonitor_Workforce1] Is Not Null,[aMonitor_Workforce1],True,[Monitor_Workforce1]), Switch([bMonitor_Workforce2] Is Not Null,[bMonitor_Workforce2],[aMonitor_Workforce2] Is Not Null,[aMonitor_Workforce2],True,[Monitor_Workforce2]), tDef_GL_CostCentres.[Cost Centre Type], IIf(([Source] Like "CIP*") And ([Source] Not Like "*_RT"),IIf(InStr(1,[Comment],":")=0,Null,Left([Comment],InStr(1,[Comment],":")-1)),Null);


Any help would be much appreciated!

 

[plog]

A mismatch error means that the datatype of a field is being used in a way it is not intended (summing strings, linking a text field to a number field, sorting on an unsortable data type). That means the error could be in a calculated field in the SELECT clause or between two fields in the FROM clause that are not the same datatype.

Here's what I would do--in design view I would delete a datasource from the query and try and run it. If I got the error, I would delete another table until I didn't get that error. That will tell you the offending datasource. If that datasource is a sub-query I would see if it will run by itself. If it does that means the error truly is in the query you posted, if not it means that entire query is hosed and you should debug it in the same manner.

If you've identified the offending datasource and that datasource is valid by itself, first make sure that the join between it and the rest of the query is between similar datatypes. Make sure you aren't linking a text field in the offending data source to a number in another datasource. Once you've done that and the query still gives you the same error its time to start looking at calculated fields in the query you posted based on fields from the offending datasource. I'd use the same delete and run method for tables, but this time for individual fields until you've identified the offending one.

 

[The_Doc_Man]

Whoever wrote that is a cruel person.

I'll tell you how to approach that. Copy/paste that puppy into a wordpad document. Take each section apart a little bit at a time because deciphering a wall of text is a sure way to go blind. Here is an example:

Sum(qry_Union_ExportW1.Planning_Outturn) AS Planning_Outturn, Sum(qry_Union_ExportW1.Actual_WTE) AS Actual_WTE, Sum(IIf([Period_12]=0,0,[qry_Union_ExportW1].[Actual_WTE])) AS Closing_WTE, Sum(qry_Union_ExportW1.Annual_Budget) AS Annual_Budget, Sum(qry_Union_ExportW1.Funded_WTE) AS Funded_WTE, Sum(qry_Union_ExportW1.ForecastOutturn) AS ForecastOutturn, Sum(qry_Union_ExportW1.PrRata) AS M12RunRate,

Break it apart like this:

Sum(qry_Union_ExportW1.Planning_Outturn) AS Planning_Outturn,

Sum(qry_Union_ExportW1.Actual_WTE) AS Actual_WTE,

Sum(IIf([Period_12]=0,0,[qry_Union_ExportW1].[Actual_WTE])) AS Closing_WTE,

Sum(qry_Union_ExportW1.Annual_Budget) AS Annual_Budget,

Sum(qry_Union_ExportW1.Funded_WTE) AS Funded_WTE,

Sum(qry_Union_ExportW1.ForecastOutturn) AS ForecastOutturn,

Sum(qry_Union_ExportW1.PrRata) AS M12RunRate,

Now one piece at a time, look up the data type of the underlying element. For example, in that last line, is qry_Union_ExportW1.PrRata a numeric or text field? You can't SUM a text field. The part that is overwhelming is how much of it there is. When you break it up, you follow Caesar's rule of Divide and Conquer.