SQL help
I get a data type mismatch in criteria expression when I try to run the below SQL as my reports record source.
Now in the stl statement below, the WHERE statement is my only addition. GLOBALREGIONCODE is a variant and the DIRECTORCODE being compared against it is a LONG INTEGER, is there a conflict here? Please advise.
strSQL = "SELECT qryTypeMonthMgrCode.promotionType, qryTypeMonthMgrCode.Month, qryTypeMonthMgrCode.Code, qryTypeMonthMgrCode.ManagerName, qryTypeMonthMgrCode.DirectorCode, Sum(NewQuery.ActivationForecast) AS SumOfActivationForecast, Sum(NewQuery.Results) AS SumOfResults, Sum(NewQuery.TotalCommits) AS SumOfTotalCommits, Sum(NewQuery.TotalPaid) AS SumOfTotalPaid, Sum(NewQuery.Accrual) AS SumOfAccrual, Sum(NewQuery.ProjectedSpending) AS SumOfProjectedSpending FROM qryTypeMonthMgrCode LEFT JOIN NewQuery ON (qryTypeMonthMgrCode.Month = NewQuery.tempMonth) AND (qryTypeMonthMgrCode.Code = NewQuery.Code) AND (qryTypeMonthMgrCode.promotionType = NewQuery.PromotionType)" _
& " WHERE qryTypeMonthMgrCode.DirectorCode='" & globalregioncode & "' GROUP BY qryTypeMonthMgrCode.promotionType, qryTypeMonthMgrCode.Month, qryTypeMonthMgrCode.Code, qryTypeMonthMgrCode.ManagerName, qryTypeMonthMgrCode.DirectorCode HAVING (((qryTypeMonthMgrCode.Code) <> 1000 And (qryTypeMonthMgrCode.Code) <> 1107 And (qryTypeMonthMgrCode.Code) <> 1108 And (qryTypeMonthMgrCode.Code) <> 1109 And (qryTypeMonthMgrCode.Code) <> 1202 And (qryTypeMonthMgrCode.Code) <> 1306 And (qryTypeMonthMgrCode.Code) <> 1307 And (qryTypeMonthMgrCode.Code) <> 1308 And (qryTypeMonthMgrCode.Code) <> 1309 And (qryTypeMonthMgrCode.Code) <= 1504)) ORDER BY qryTypeMonthMgrCode.promotionType, qryTypeMonthMgrCode.Month, qryTypeMonthMgrCode.Code;"
I get a data type mismatch in criteria expression when I try to run the below SQL as my reports record source.
Now in the stl statement below, the WHERE statement is my only addition. GLOBALREGIONCODE is a variant and the DIRECTORCODE being compared against it is a LONG INTEGER, is there a conflict here? Please advise.
strSQL = "SELECT qryTypeMonthMgrCode.promotionType, qryTypeMonthMgrCode.Month, qryTypeMonthMgrCode.Code, qryTypeMonthMgrCode.ManagerName, qryTypeMonthMgrCode.DirectorCode, Sum(NewQuery.ActivationForecast) AS SumOfActivationForecast, Sum(NewQuery.Results) AS SumOfResults, Sum(NewQuery.TotalCommits) AS SumOfTotalCommits, Sum(NewQuery.TotalPaid) AS SumOfTotalPaid, Sum(NewQuery.Accrual) AS SumOfAccrual, Sum(NewQuery.ProjectedSpending) AS SumOfProjectedSpending FROM qryTypeMonthMgrCode LEFT JOIN NewQuery ON (qryTypeMonthMgrCode.Month = NewQuery.tempMonth) AND (qryTypeMonthMgrCode.Code = NewQuery.Code) AND (qryTypeMonthMgrCode.promotionType = NewQuery.PromotionType)" _
& " WHERE qryTypeMonthMgrCode.DirectorCode='" & globalregioncode & "' GROUP BY qryTypeMonthMgrCode.promotionType, qryTypeMonthMgrCode.Month, qryTypeMonthMgrCode.Code, qryTypeMonthMgrCode.ManagerName, qryTypeMonthMgrCode.DirectorCode HAVING (((qryTypeMonthMgrCode.Code) <> 1000 And (qryTypeMonthMgrCode.Code) <> 1107 And (qryTypeMonthMgrCode.Code) <> 1108 And (qryTypeMonthMgrCode.Code) <> 1109 And (qryTypeMonthMgrCode.Code) <> 1202 And (qryTypeMonthMgrCode.Code) <> 1306 And (qryTypeMonthMgrCode.Code) <> 1307 And (qryTypeMonthMgrCode.Code) <> 1308 And (qryTypeMonthMgrCode.Code) <> 1309 And (qryTypeMonthMgrCode.Code) <= 1504)) ORDER BY qryTypeMonthMgrCode.promotionType, qryTypeMonthMgrCode.Month, qryTypeMonthMgrCode.Code;"