I have the following query, which runs perfectly:
When I try to reverse the above, as shown
I get the error message "The field is too small to accept the amount of data you are trying to add. try inserting or pasting less data." I'm not attempting to run an insert or update query.
Access Help lives up to expectations by just repeating the error message, and the posts I can find on the forum which mention this error all seem to include something about a maximum of 254 characters in a field. None of mine are anywhere near that amount.
Does anyone have any idea what could be causing this?
Code:
SELECT
[qry_BMO_District_Recap - RHS - Stage 2].Transit,
Format([qry_BMO_District_Recap - RHS - Stage 2].month,"mmmm yyyy") AS MonthOfYear,
[qry_BMO_District_Recap - RHS - Stage 2].DISTNAME AS District, [qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name, [qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Count) AS M_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Amount) AS M_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count) AS Y_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount) AS Y_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Count) AS M_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Amount) AS M_BtoN_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count) AS Y_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount) AS Y_BtoN_Amount,
[qry_BMO_District_Recap - RHS - Stage 2].AREANAME, [qry_BMO_District_Recap - RHS - Stage 2].Financial_Year
FROM [qry_BMO_District_Recap - RHS - Stage 2] LEFT JOIN [qry_BMO_District_Recap - LHS - Stage 2]
ON ([qry_BMO_District_Recap - RHS - Stage 2].AREANAME = [qry_BMO_District_Recap - LHS - Stage 2].AREANAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Month = [qry_BMO_District_Recap - LHS - Stage 2].Month)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Transit = [qry_BMO_District_Recap - LHS - Stage 2].Transit)
AND ([qry_BMO_District_Recap - RHS - Stage 2].DISTNAME = [qry_BMO_District_Recap - LHS - Stage 2].DISTNAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Financial_Year = [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year);
Code:
SELECT
[qry_BMO_District_Recap - LHS - Stage 2].Transit,
Format([qry_BMO_District_Recap - LHS - Stage 2].month,"mmmm yyyy") AS MonthOfYear,
[qry_BMO_District_Recap - LHS - Stage 2].DISTNAME AS District, [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name, [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Count) AS M_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Month_Amount) AS M_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Count) AS Y_NtoB_Count, IIf(IsNull([qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - RHS - Stage 2].Yearly_Amount) AS Y_NtoB_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Count) AS M_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Month_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Month_Amount) AS M_BtoN_Amount,
IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Count) AS Y_BtoN_Count, IIf(IsNull([qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount),0,[qry_BMO_District_Recap - LHS - Stage 2].Yearly_Amount) AS Y_BtoN_Amount,
[qry_BMO_District_Recap - LHS - Stage 2].AREANAME, [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year
FROM [qry_BMO_District_Recap - LHS - Stage 2] LEFT JOIN [qry_BMO_District_Recap - RHS - Stage 2]
ON ([qry_BMO_District_Recap - RHS - Stage 2].AREANAME = [qry_BMO_District_Recap - LHS - Stage 2].AREANAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Month = [qry_BMO_District_Recap - LHS - Stage 2].Month)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Transit = [qry_BMO_District_Recap - LHS - Stage 2].Transit)
AND ([qry_BMO_District_Recap - RHS - Stage 2].DISTNAME = [qry_BMO_District_Recap - LHS - Stage 2].DISTNAME)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Name = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Name)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Grouping_Priority = [qry_BMO_District_Recap - LHS - Stage 2].Grouping_Priority)
AND ([qry_BMO_District_Recap - RHS - Stage 2].Financial_Year = [qry_BMO_District_Recap - LHS - Stage 2].Financial_Year);
Access Help lives up to expectations by just repeating the error message, and the posts I can find on the forum which mention this error all seem to include something about a maximum of 254 characters in a field. None of mine are anywhere near that amount.
Does anyone have any idea what could be causing this?