Getting a Data Type Mismatch error... I can't see why

Colin@Toyota · Jan 29, 2009

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...

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

namliam · Jan 29, 2009

I cannot blaim you for not seeing it, this is a mess.... You really really need to put this into a format that is more readable!

possiblities..
[TRANSACTION_ID]='25'

ID is generaly a number field.

[LASTYR_SALES]='0'
Looks like this is a number field too?

Numbers are not enclosed by quotes or anything at all.

Colin@Toyota · Jan 29, 2009

Ah..
I realize after I posted it that I copied from the VBA editor, not the nicely formatted word file I had.

But that may be part of the issue... the tables I am calling from are old, and most fields are text fields... including the TRANSACTION_ID... but not SALES.

I will try that.

NOTE: I modified the SQL slightly, and turned it into a make table query and manually entered all the dates and it is running right now... just taking forever.

I'll post up some nicer code in a moment.

Colin@Toyota · Jan 29, 2009

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

namliam · Jan 29, 2009

So its fixed??

Tip:
If you are doing big SQLs like this dont use the continuation like you are doing now.

mySQL = ""
mySQL = mySQL & "Select..."
mySQL = mySQL & "etc..."
mySQL = mySQL & "etc..."
mySQL = mySQL & "from..."
mySQL = mySQL & "etc..."
mySQL = mySQL & "etc..."
mySQL = mySQL & "etc..."

Will produce a very readable and maintainable structure that will always work.

Colin@Toyota · Jan 29, 2009

No, its not fixed. I'm trying just to simplify the query first and get that to not take forever to run, and then I will revisit the Module.

I like that mySQL trick though, thanks!

Husan · Jan 29, 2009

Could well be the date variable in the sql statement:

Between " & ReportLastYear & ReportMonth & ReportStartDay & " AND " & ReportLastYear & ReportMonth & ReportEndDay & " )) " & _

Dates are passed using the following format:

Between #30/01/2009# AND #30/03/2009#
so your statement should look something like this:

Between #" & ReportLastYear & "/" & ReportMonth & "/" & ReportStartDay & "# AND #" & ReportLastYear & "/" & ReportMonth & "/" & ReportEndDay & "# )) " & _

Tip: put a stop after the sql statement and look in the immediate window and type:
?mySQL
then copy and past this in the SQL view in query design to find out where the problem is.

Colin@Toyota · Jan 29, 2009

Husan said:
Could well be the date variable in the sql statement:

Between " & ReportLastYear & ReportMonth & ReportStartDay & " AND " & ReportLastYear & ReportMonth & ReportEndDay & " )) " & _

Dates are passed using the following format:

Between #30/01/2009# AND #30/03/2009#
so your statement should look something like this:

Between #" & ReportLastYear & "/" & ReportMonth & "/" & ReportStartDay & "# AND #" & ReportLastYear & "/" & ReportMonth & "/" & ReportEndDay & "# )) " & _

Unfortunately, the tables I am accessing are ancient, and store the date as text in the format yyyymmdd.

Husan said:
Tip: put a stop after the sql statement and look in the immediate window and type:
?mySQL
then copy and past this in the SQL view in query design to find out where the problem is.

How do I put a stop in?

Husan · Jan 29, 2009

to put a stop just type in STOP after the sql statement

you can format the date as follows (just change bthe day and year around:

Between #" & ReportStartDay & "/" & ReportMonth & "/" & ReportLastYear & "# AND #" & ReportEndDay & "/" & ReportMonth & "/" & ReportLastYear & "# )) " & _

Colin@Toyota · Jan 29, 2009

Ok, so I haven't managed to get it working...

And when I try running it as a straight query, it takes forever, and doesnt do anything.

So, I have re-written the module, and broken what I need to do down into 4 simple make-table queries, and to run the sequentially. Then I will just create a query based on those tables, and make a report based on that.

gemma-the-husky · Jan 30, 2009

colin, can yuo not construct a stored query by design, visually, and run that - or if necessary cut and paste the sql, when its working

a stored query will be more efficient than a sql statement anyway

its just so hard to read statements like that

Getting a Data Type Mismatch error... I can't see why

Colin@Toyota

What's an Access?

namliam

The Mailman - AWF VIP

Colin@Toyota

What's an Access?

Colin@Toyota

What's an Access?

namliam

The Mailman - AWF VIP

Colin@Toyota

What's an Access?

Husan

Registered User.

Colin@Toyota

What's an Access?

Husan

Registered User.

Colin@Toyota

What's an Access?

gemma-the-husky

Super Moderator

Users who are viewing this thread