MHarkess MHarkess - 5 months ago 18
SQL Question

Run-time error: Invalid parameter 1 specified for datepart - VBA - SQL

I'm running some code in Excel VBA which queries a database then brings the data into Excel for formatting.

It worked fine the last time I ran it (that old chestnut) but today I've come to run the monthly report and it is throwing up a datepart error as


Invalid parameter 1 specified for datepart


Here is the code:

StrQuery = "SELECT dbo_MANUFACTURER.COMPANY_NAME AS BRAND, dbo_COMPANY.COMPANY_NAME, dbo_AGENTS.SHORT_DESC AS AGENT, dbo_STOCK_SUB_TYPE.SHORT_DESC AS [STOCK TYPE], Replace(Replace(dbo_COMPANY.CURRENCY_ID,'110','EURO'),'1','GBP') AS [CURRENCY]," & _
"Sum(([Unit_Net]*[QTY_SOLD])-[DELIVERY_TOTAL]) AS [NET TOTAL], dbo_SORDER.DATE_CREATED, dbo_SORDER.SORDER_CODE FROM (((((dbo_COMPANY INNER JOIN dbo_SORDER ON dbo_COMPANY.COMPANY_ID = dbo_SORDER.COMPANY_ID) INNER JOIN " & _
"dbo_SORDER_ITEM ON dbo_SORDER.SORDER_ID = dbo_SORDER_ITEM.SORDER_ID) INNER JOIN dbo_STOCK ON dbo_SORDER_ITEM.STOCK_ID = dbo_STOCK.STOCK_ID) INNER JOIN dbo_STOCK_SUB_TYPE ON dbo_STOCK.SSTYPE_ID = dbo_STOCK_SUB_TYPE.SSTYPE_ID) INNER JOIN " & _
"dbo_AGENTS ON dbo_COMPANY.AGENT_ID = dbo_AGENTS.AGENT_ID) INNER JOIN dbo_MANUFACTURER ON dbo_STOCK.MANUF_ID = dbo_MANUFACTURER.MANUF_ID GROUP BY dbo_MANUFACTURER.COMPANY_NAME, dbo_COMPANY.COMPANY_NAME, dbo_AGENTS.SHORT_DESC, " & _
"dbo_STOCK_SUB_TYPE.SHORT_DESC, dbo_SORDER.DATE_CREATED, dbo_SORDER.SORDER_CODE, dbo_COMPANY.CURRENCY_ID, dbo_STOCK.MANUF_ID HAVING (((dbo_STOCK_SUB_TYPE.SHORT_DESC) <> " & "'EMBROIDERY'" & ") And ((Year([DATE_CREATED]) * 12 + " & _
"DatePart(" & "'m'" & ", [DATE_CREATED])) = Year(Date) * 12 + DatePart(" & "'m'" & ", Date) - 1)) ORDER BY dbo_COMPANY.COMPANY_NAME, dbo_SORDER.DATE_CREATED;"


Apologies for the mass of text on long lines.

As mentioned the code throws up the error: Invalid parameter 1 specified for date part. I've tried converting the dates as someone mentioned on a forum post but that hasn't been successful.

If someone has any ideas that would be amazing!

Answer

Simply try with the below query

SELECT DATEPART('m', GETDATE())

returns the

Invalid parameter 1 specified for datepart.

As per DATEPART description from MSDN, the datepart abbreviation can be m or mm for MONTH, no need of the single quote around the 'm'.

Remove the single quote will solve your problem.


So your working query will be:

SELECT dbo_MANUFACTURER.COMPANY_NAME AS BRAND, 
      dbo_COMPANY.COMPANY_NAME, dbo_AGENTS.SHORT_DESC AS AGENT, 
      dbo_STOCK_SUB_TYPE.SHORT_DESC AS [STOCK TYPE], 
      Replace(Replace(dbo_COMPANY.CURRENCY_ID,'110','EURO'),'1','GBP') AS [CURRENCY],
      Sum(([Unit_Net]*[QTY_SOLD])-[DELIVERY_TOTAL]) AS [NET TOTAL], 
      dbo_SORDER.DATE_CREATED, 
      dbo_SORDER.SORDER_CODE

FROM ((((
(dbo_COMPANY INNER JOIN dbo_SORDER ON dbo_COMPANY.COMPANY_ID = dbo_SORDER.COMPANY_ID) 
INNER JOIN dbo_SORDER_ITEM ON dbo_SORDER.SORDER_ID = dbo_SORDER_ITEM.SORDER_ID) 
INNER JOIN dbo_STOCK ON dbo_SORDER_ITEM.STOCK_ID = dbo_STOCK.STOCK_ID) 
INNER JOIN dbo_STOCK_SUB_TYPE ON dbo_STOCK.SSTYPE_ID = dbo_STOCK_SUB_TYPE.SSTYPE_ID) 
INNER JOIN dbo_AGENTS ON dbo_COMPANY.AGENT_ID = dbo_AGENTS.AGENT_ID) 
INNER JOIN dbo_MANUFACTURER ON dbo_STOCK.MANUF_ID = dbo_MANUFACTURER.MANUF_ID 

GROUP BY dbo_MANUFACTURER.COMPANY_NAME, 
         dbo_COMPANY.COMPANY_NAME, dbo_AGENTS.SHORT_DESC, 
         dbo_STOCK_SUB_TYPE.SHORT_DESC, 
         dbo_SORDER.DATE_CREATED, 
         dbo_SORDER.SORDER_CODE, 
         dbo_COMPANY.CURRENCY_ID, 
         dbo_STOCK.MANUF_ID 

HAVING (((dbo_STOCK_SUB_TYPE.SHORT_DESC) <> 'EMBROIDERY') 
        And ((Year([DATE_CREATED]) * 12 
              + DatePart(m, [DATE_CREATED])) = Year(Date) * 12 
              + DatePart(m, Date) - 1)) 

ORDER BY dbo_COMPANY.COMPANY_NAME, 
         dbo_SORDER.DATE_CREATED;