Hello,

I wonder if someone can see where I've gone wrong with this SQL.
This is the control source of a listbox.
The first set of code works, but the second set of code (where I've formatted a field), says data mismatch.

Thanks for looking J


SELECT C20_tbl1aC200_Target_Details.Agent, PIC_tblPIC.[Agency Name] AS Agency, 'FY' & [FY] AS [F Y], C20_tbl1aC200_Target_Details.CURR AS Cur, Sum(C20_tbl1bC200_Target.Target) AS Target, Sum(C20_tbl1C200_Result.Result) AS Result, [Result]-[Target] AS Var, [Agent] & [FY] AS Ref
FROM ((C20_tbl1aC200_Target_Details LEFT JOIN C20_tbl1bC200_Target ON C20_tbl1aC200_Target_Details.[Item ID] = C20_tbl1bC200_Target.[Item ID]) LEFT JOIN PIC_tblPIC ON C20_tbl1aC200_Target_Details.Agent = PIC_tblPIC.[AG Code]) LEFT JOIN C20_tbl1C200_Result ON (C20_tbl1bC200_Target.[Item ID] = C20_tbl1C200_Result.[Item ID]) AND (C20_tbl1bC200_Target.MNTH = C20_tbl1C200_Result.MNTH)
GROUP BY C20_tbl1aC200_Target_Details.Agent, PIC_tblPIC.[Agency Name], 'FY' & [FY], C20_tbl1aC200_Target_Details.CURR, [Agent] & [FY]
HAVING (((C20_tbl1aC200_Target_Details.Agent) Like [Forms]![MNU_frm1_Menu]![MNU_subfrm].[Form]![C20_cbo2]))
ORDER BY C20_tbl1aC200_Target_Details.Agent, 'FY' & [FY];



SELECT C20_tbl1aC200_Target_Details.Agent, PIC_tblPIC.[Agency Name] AS Agency, 'FY' & [FY] AS [F Y], C20_tbl1aC200_Target_Details.CURR AS Cur, Sum(Format([C20_tbl1bC200_Target]![Target],"###,###,###")) AS Target, Sum(C20_tbl1C200_Result.Result) AS Result, [Result]-[Target] AS Var, [Agent] & [FY] AS Ref
FROM ((C20_tbl1aC200_Target_Details LEFT JOIN C20_tbl1bC200_Target ON C20_tbl1aC200_Target_Details.[Item ID] = C20_tbl1bC200_Target.[Item ID]) LEFT JOIN PIC_tblPIC ON C20_tbl1aC200_Target_Details.Agent = PIC_tblPIC.[AG Code]) LEFT JOIN C20_tbl1C200_Result ON (C20_tbl1bC200_Target.[Item ID] = C20_tbl1C200_Result.[Item ID]) AND (C20_tbl1bC200_Target.MNTH = C20_tbl1C200_Result.MNTH)
GROUP BY C20_tbl1aC200_Target_Details.Agent, PIC_tblPIC.[Agency Name], 'FY' & [FY], C20_tbl1aC200_Target_Details.CURR, [Agent] & [FY]
ORDER BY C20_tbl1aC200_Target_Details.Agent, 'FY' & [FY];

Format will return a STRING value so you should SUM them and THEN format (put the format on the outside of the Sum. And you may need to use the NZ function to handle nulls.