Ignore missing fields in MAX query.

[Lochwood]

I have a crosstab query that is dependent on check boxes and feeds a max query. sometimes columns dont exist due to boxes not getting ticked. How can i ignore a field in the max query if its not present. currently shows an error because it cant find it in the mAX query.

 

[cheekybuddha]

Please post the SQL of your query

 

[Lochwood]

Please post the SQL of your query

Crosstab Query
PARAMETERS [Forms]![Work]![Job Number] Short, [Forms]![Work]![Cancelled_Check] Bit;
TRANSFORM First(DateAdd("yyyy",+3,[Date_Last_Dem])) AS Dem_Due
SELECT Historic_Assignments_Query.Assignee
FROM Historic_Assignments_Query INNER JOIN (Comp_RPM INNER JOIN Comp_RPM_Reg ON Comp_RPM.Comp_RPM_ID = Comp_RPM_Reg.Comp_RPM_ID) ON Historic_Assignments_Query.Employee_No = Comp_RPM_Reg.Employee_No
WHERE (((Comp_RPM.Comp_RPM_No)=DLookUp("1","Work_RPM_Reg_Required_Query","[1]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("2","Work_RPM_Reg_Required_Query","[2]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("3","Work_RPM_Reg_Required_Query","[3]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("4","Work_RPM_Reg_Required_Query","[4]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("5","Work_RPM_Reg_Required_Query","[5]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("6","Work_RPM_Reg_Required_Query","[6]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("7","Work_RPM_Reg_Required_Query","[7]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("8","Work_RPM_Reg_Required_Query","[8]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("9","Work_RPM_Reg_Required_Query","[9]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("10","Work_RPM_Reg_Required_Query","[10]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("11","Work_RPM_Reg_Required_Query","[11]=True") Or (Comp_RPM.Comp_RPM_No)=DLookUp("12","Work_RPM_Reg_Required_Query","[12]=True")))
GROUP BY Historic_Assignments_Query.Assignee, Comp_RPM.Comp_RPM_No, Comp_RPM_Reg.Comp_RPM_Reg_ID, Comp_RPM_Reg.Comp_RPM_ID, Comp_RPM_Reg.Date_Last_Dem, Comp_RPM_Reg.Prog_Dem_To
PIVOT Comp_RPM.Comp_RPM_Title;


Max Query that looks at above. sometimes not all Dlookup Filed are true so dont exist when max query runs.

SELECT Work_RPM_Comps_Crosstab_Query.Assignee, Max(Work_RPM_Comps_Crosstab_Query.[TASK1]) AS [MaxOfTASK1], Max(Work_RPM_Comps_Crosstab_Query.[TASK2]) AS [MaxOfTASK2], Max(Work_RPM_Comps_Crosstab_Query.[TASK3]) AS [MaxOfTASK3], Max(Work_RPM_Comps_Crosstab_Query.[TASK4]) AS [MaxOfTASK4], Max(Work_RPM_Comps_Crosstab_Query.[TASK5]) AS [MaxOfTASK5], Max(Work_RPM_Comps_Crosstab_Query.[TASK6]) AS [MaxOfTASK6], Max(Work_RPM_Comps_Crosstab_Query.[TASK7]) AS [MaxOfTASK7], Max(Work_RPM_Comps_Crosstab_Query.[TASK8]) AS [MaxOfTASK8], Max(Work_RPM_Comps_Crosstab_Query.[TASK9]) AS [MaxOfTASK9], Max(Work_RPM_Comps_Crosstab_Query.[TASK10]) AS [MaxOfTASK10], Max(Work_RPM_Comps_Crosstab_Query.[TASK11]) AS [MaxOfTASK11], Max(Work_RPM_Comps_Crosstab_Query.[TASK12]) AS [MaxOfTASK12]
FROM Work_RPM_Comps_Crosstab_Query
GROUP BY Work_RPM_Comps_Crosstab_Query.Assignee;

 

[cheekybuddha]

Hi,

Untested, but try changing the PIVOT line in the crosstab to:

-- ...
PIVOT Comp_RPM.Comp_RPM_Title
  IN ('TASK1','TASK2','TASK3','TASK4','TASK5','TASK6','TASK7','TASK8','TASK9','TASK10','TASK11','TASK12');