Colin@Toyota
What's an Access?
- Local time
- Yesterday, 20:23
- Joined
- May 2, 2006
- Messages
- 203
Hi guys,
I created this module, and when I try to call the public function ProductPerformance, I get a datatype mismatch error. I'm hoping a fresh set of eyes might help me find the issue...
Cheers,
Colin
I created this module, and when I try to call the public function ProductPerformance, I get a datatype mismatch error. I'm hoping a fresh set of eyes might help me find the issue...
Code:
Option Compare Database
Public Function ProductPerformance()
On Error GoTo Err_ProductPerformance
Dim ReportThisYear, ReportLastYear, ReportMonth, ReportStartDay, ReportEndDay
ReportThisYear = YEAR(DateAdd("m", -1, DATE)) 'get the year, from (today's date -1 month) --> this should allow for reports in Jan to be run for Dec
ReportLastYear = YEAR(DateAdd("m", -1, DATE)) - 1 'get last year, from (today's date -1 month) --> this should allow for reports in Jan to be run for Dec
ReportMonth = Format(DateAdd("m", -1, DATE), "mm") 'get the month, from (today's date -1 month), format as "mm"
ReportStartDay = "01" 'gather data starting from the first day of the month
ReportEndDay = Format(day(DateAdd("m", -1, DATE)), "00") 'gather data ending on the last day of the month
strProdPerSQL = "SELECT PROD_PERF.PRC_DESCRIPTION, PROD_PERF.MONTH_UNITS, PROD_PERF.MONTH_SALES, Sum(IIf([PROD_PERF].[LASTYR_SALES]='0', " - ", ([PROD_PERF].[MONTH_SALES]-[PROD_PERF].[LASTYR_SALES])/[PROD_PERF].[LASTYR_SALES])) AS [MTH_PERCENT_CHANGE], PROD_PERF.YTD_UNITS, PROD_PERF.YTD_SALES, Sum(IIf([PROD_PERF].[LASTYTD_SALES]='0', " - ", ([PROD_PERF].[YTD_SALES]-[PROD_PERF].[LASTYTD_SALES])/[PROD_PERF].[LASTYTD_SALES])) AS [YTD_PERCENT_CHANGE] " & _
"FROM " & _
"(SELECT DWP_D_PART.PRC_DESCRIPTION, Sum(IIf([DWP_F_PART_INVOICE]![TRANSACTION_ID]='25', [DWP_F_PART_INVOICE]![QTY_SHIPPED], -1*[DWP_F_PART_INVOICE]![QTY_SHIPPED])) AS [MONTH_UNITS], Sum(IIf([DWP_F_PART_INVOICE]![TRANSACTION_ID]='25', [DWP_F_PART_INVOICE]![QTY_SHIPPED]*[DWP_F_PART_INVOICE]![DLR_NET], -1*[DWP_F_PART_INVOICE]![QTY_SHIPPED]*[DWP_F_PART_INVOICE]![DLR_NET])) AS [MONTH_SALES] " & _
"FROM DPATOPS_TGW_ANALYST_CODE INNER JOIN (DWP_D_PART INNER JOIN DWP_F_PART_INVOICE ON DWP_D_PART.PART_SID = DWP_F_PART_INVOICE.PART_SID) ON DPATOPS_TGW_ANALYST_CODE.GW_ANLST_CD = DWP_D_PART.ORDER_ANALYST_CODE " & _
"WHERE (((DWP_D_PART.ORDER_ANALYST_CODE) In (SELECT DWP_D_PART_ANALYST.ANALYST_CODE FROM DWP_D_PART_ANALYST WHERE (((DWP_D_PART_ANALYST.ANALYST_CODE_GROUP)='CDM')) GROUP BY DWP_D_PART_ANALYST.ANALYST_CODE))) " & _
"GROUP BY DWP_D_PART.PRC_DESCRIPTION, DWP_F_PART_INVOICE.INVOICE_DATE_SID " & _
"HAVING (((DWP_F_PART_INVOICE.INVOICE_DATE_SID) Between " & ReportThisYear & ReportMonth & ReportStartDay & " AND " & ReportThisYear & ReportMonth & ReportEndDay & " )) " & _
"UNION ALL " & _
"SELECT DWP_D_PART.PRC_DESCRIPTION, Sum(IIf([DWP_F_PART_INVOICE]![TRANSACTION_ID]='25', [DWP_F_PART_INVOICE]![QTY_SHIPPED], -1*[DWP_F_PART_INVOICE]![QTY_SHIPPED])) AS [LASTYR_UNITS], Sum(IIf([DWP_F_PART_INVOICE]![TRANSACTION_ID]='25', [DWP_F_PART_INVOICE]![QTY_SHIPPED]*[DWP_F_PART_INVOICE]![DLR_NET], -1*[DWP_F_PART_INVOICE]![QTY_SHIPPED]*[DWP_F_PART_INVOICE]![DLR_NET])) AS [LASTYR_SALES] " & _
"FROM DPATOPS_TGW_ANALYST_CODE INNER JOIN (DWP_D_PART INNER JOIN DWP_F_PART_INVOICE ON DWP_D_PART.PART_SID = DWP_F_PART_INVOICE.PART_SID) ON DPATOPS_TGW_ANALYST_CODE.GW_ANLST_CD = DWP_D_PART.ORDER_ANALYST_CODE " & _
"WHERE (((DWP_D_PART.ORDER_ANALYST_CODE) In (SELECT DWP_D_PART_ANALYST.ANALYST_CODE FROM DWP_D_PART_ANALYST WHERE (((DWP_D_PART_ANALYST.ANALYST_CODE_GROUP)='CDM')) GROUP BY DWP_D_PART_ANALYST.ANALYST_CODE))) " & _
"GROUP BY DWP_D_PART.PRC_DESCRIPTION, DWP_F_PART_INVOICE.INVOICE_DATE_SID " & _
"HAVING (((DWP_F_PART_INVOICE.INVOICE_DATE_SID) Between " & ReportLastYear & ReportMonth & ReportStartDay & " AND " & ReportLastYear & ReportMonth & ReportEndDay & " )) " & _
"UNION ALL " & _
"SELECT DWP_D_PART.PRC_DESCRIPTION, Sum(IIf([DWP_F_PART_INVOICE]![TRANSACTION_ID]='25', [DWP_F_PART_INVOICE]![QTY_SHIPPED], -1*[DWP_F_PART_INVOICE]![QTY_SHIPPED])) AS [YTD_UNITS], Sum(IIf([DWP_F_PART_INVOICE]![TRANSACTION_ID]='25', [DWP_F_PART_INVOICE]![QTY_SHIPPED]*[DWP_F_PART_INVOICE]![DLR_NET], -1*[DWP_F_PART_INVOICE]![QTY_SHIPPED]*[DWP_F_PART_INVOICE]![DLR_NET])) AS [YTD_SALES] " & _
"FROM DPATOPS_TGW_ANALYST_CODE INNER JOIN (DWP_D_PART INNER JOIN DWP_F_PART_INVOICE ON DWP_D_PART.PART_SID = DWP_F_PART_INVOICE.PART_SID) ON DPATOPS_TGW_ANALYST_CODE.GW_ANLST_CD = DWP_D_PART.ORDER_ANALYST_CODE " & _
"WHERE (((DWP_D_PART.ORDER_ANALYST_CODE) In (SELECT DWP_D_PART_ANALYST.ANALYST_CODE FROM DWP_D_PART_ANALYST WHERE (((DWP_D_PART_ANALYST.ANALYST_CODE_GROUP)='CDM')) GROUP BY DWP_D_PART_ANALYST.ANALYST_CODE))) " & _
"GROUP BY DWP_D_PART.PRC_DESCRIPTION, DWP_F_PART_INVOICE.INVOICE_DATE_SID " & _
"HAVING (((DWP_F_PART_INVOICE.INVOICE_DATE_SID) Between " & ReportThisYear & 0 & 1 & ReportStartDay & " AND " & ReportThisYear & 12 & ReportEndDay & " )) " & _
"UNION ALL " & _
"SELECT DWP_D_PART.PRC_DESCRIPTION, Sum(IIf([DWP_F_PART_INVOICE]![TRANSACTION_ID]='25', [DWP_F_PART_INVOICE]![QTY_SHIPPED], -1*[DWP_F_PART_INVOICE]![QTY_SHIPPED])) AS [LASTYTD_UNITS], Sum(IIf([DWP_F_PART_INVOICE]![TRANSACTION_ID]='25', [DWP_F_PART_INVOICE]![QTY_SHIPPED]*[DWP_F_PART_INVOICE]![DLR_NET], -1*[DWP_F_PART_INVOICE]![QTY_SHIPPED]*[DWP_F_PART_INVOICE]![DLR_NET])) AS [LASTYTD_SALES] " & _
"FROM DPATOPS_TGW_ANALYST_CODE INNER JOIN (DWP_D_PART INNER JOIN DWP_F_PART_INVOICE ON DWP_D_PART.PART_SID = DWP_F_PART_INVOICE.PART_SID) ON DPATOPS_TGW_ANALYST_CODE.GW_ANLST_CD = DWP_D_PART.ORDER_ANALYST_CODE " & _
"WHERE (((DWP_D_PART.ORDER_ANALYST_CODE) In (SELECT DWP_D_PART_ANALYST.ANALYST_CODE FROM DWP_D_PART_ANALYST WHERE (((DWP_D_PART_ANALYST.ANALYST_CODE_GROUP)='CDM')) GROUP BY DWP_D_PART_ANALYST.ANALYST_CODE))) " & _
"GROUP BY DWP_D_PART.PRC_DESCRIPTION, DWP_F_PART_INVOICE.INVOICE_DATE_SID " & _
"HAVING (((DWP_F_PART_INVOICE.INVOICE_DATE_SID) Between " & ReportLastYear & 0 & 1 & ReportStartDay & " AND " & ReportLastYear & 12 & ReportEndDay & " ))) ORDER BY [MTH_PERCENT_CHANGE] AS PROD_PERF; "
Set sqlValue = CurrentDb.QueryDefs(qryProductPerformance)
sqlValue.SQL = strProdPerSQL
DoCmd.OpenQuery "qryProductPerformance", acViewNormal
Exit_ProductPerformance:
Exit Function
Err_ProductPerformance:
MsgBox Err.Description
Resume Exit_ProductPerformance
End Function
Cheers,
Colin