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

Question

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!

EDIT:

Removing the quotes around the m in datepart does not work in Access. It pops up asking for a value for m The SQL query I have works fine in Access, I have added it below:

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;

You have DatePart(" & "'m'" & ", [DATE_CREATED])), which will give you DatePart('m', [DATE_CREATED]). Based on the error message, I am guessing that you are using SQL Server, and in SQL Server 'm' is not a valid argument for DATEPART, you either want DATEPART(MONTH, [DATE_CREATED]) (My preference as it is by far the most clear), or DATEPART(M, [DATE_CREATED]) or DATEPART(MM, [DATE_CREATED]) — GarethD
– GarethD, Commented Jul 4, 2016 at 12:26

Parfait · Accepted Answer · 2016-07-04 16:31:45Z

2

Fundamentally, you are confusing SQL dialects which use the same named function DatePart but with different arguments. Depending on architecture layer of your Excel app, you need to adjust the DatePart function according to the SQL dialect used either: ACE/Jet SQL or SQL Server TSQL.

Excel -> Access -> SQL Server (linked tables)

If Excel connects to Access database and this Access database uses SQL Server linked tables (Access objects), then you must adhere to ACE/Jet SQL dialect which requires the first argument of DatePart to be a string literal enclosed in double or single quotes as @Arulkumar shows in answer:
```
DatePart('m', [DATE_CREATED])
```
Excel -> SQL Server (ADO)

If Excel connects directly to SQL Server via ADO connection in VBA then you must adhere to SQL Server TSQL dialect which requires the first argument of DatePart to be a named value not string literal as @GarethD describes in comment:
```
DatePart(MONTH, [DATE_CREATED])
DatePart(MM, [DATE_CREATED])
DatePart(M, [DATE_CREATED])
```
Excel -> Access -> SQL Server (pass-thru)

If Excel connects to Access database calling an Access pass-through query (not linked tables) and this pass-through query (scripted and saved in Access database) connects to SQL Server via ODBC/OLEDB then you must adhere to SQL Server TSQL dialect as described above.

From the nomenclature of your tables, I believe you are connecting to Access with MSSQL linked tables as SQL Server's object qualifiers maintain schema plus table plus field separated by periods:

dbo.COMPANY.COMPANY_NAME

When linked to Access, multiple periods in table names are not allowed so by default are replaced with underscore:

dbo_COMPANY.COMPANY_NAME

The invalid object error means either the Access linked table does not exist in Access database or the table does not exist in connected SQL Server database.

answered Jul 4, 2016 at 16:31

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MHarkess Over a year ago

Hi, thanks for the info. I have tried your suggestions as per the Excel > Access Linked Tables > SQL database which is how this is set up. If I change the datepart and replace the dbo_ with dbo. ,excel/vba now gives no errors but the recordset returns no data and no error. If I output the StrQuery string into a cell then copy into Access it tells me there is a join error and highlights the first dbo. I just don't understand this as it worked fine when I ran it a few weeks ago, no errors and brought in the data fine.

Parfait Over a year ago

So in Excel you are making an ODBC connection to MS Access? If so, why are you re-naming the tables? As mentioned, Access does not allow periods in table name and so replaces it with underscore when linked tables are crated. Please post full Excel VBA code to see connection. A small change in connection string from an Access to SQL Server source could be the reason for these new errors.

Arulkumar · Accepted Answer · 2016-07-04 13:13:36Z

2

Simply try with the below query

SELECT DATEPART('m', GETDATE())

returns the

Invalid parameter 1 specified for datepart.

MSAccess DatePart, requires double quotes around the setting. So try with double quotes instead of 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;

edited Jul 4, 2016 at 13:13

answered Jul 4, 2016 at 12:34

Arulkumar

13.3k15 gold badges51 silver badges76 bronze badges

5 Comments

MHarkess Over a year ago

Thankyou for the reply, this is confusing as the sql statement works fine in Access.

MHarkess Over a year ago

It is now displaying another error: INVALID OBJECT NAME DBO_COMPANY. Again this is strange as the SQL statement I edited worked fine in Access.

MHarkess Over a year ago

Hi, sorry this does not work if I copy into Access. It comes up with Enter Parameter Value for m. However if I re-add the quotes the query works as expected.

Arulkumar Over a year ago

@MHarkess: MSAccess DatePart, requires double quotes around the setting. So try with double quote instead of single quote

MHarkess Over a year ago

Hi, thanks still getting the invalid object name dbo_COMPANY though.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related