Passing Date variables in vba to sql statement

Question

I have looked through various different scenarios already posted but none of the solutions appear to bring any joy. I am trying to pass date variables into a sql select statement but I'm given either an error about converting to date/time or the fact the variable isn't defined properly i.e. incorrect syntax. The same is applicable to the variable 'contractNumber' but I would expect however the date variable is done, the same would be applied to the 'contractNumber' variable. I have tried surrounding the variables with "" / "& / #"" & but none of those combinations seem to work....

The code I am using is below and the dates in the "Control" sheets are standard UK date - DD/MM/YYYY

Sub MEHT()

Dim startdate As Date
Dim enddate As Date
Dim contractNumber As String

startdate = Sheets("Control").Range("K8").Value
enddate = Sheets("Control").Range("K10").Value
contractNumber = Sheets("Control").Range("K13").Value

Call ConnectSqlServer

End Sub

Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=NT124431\DW;" & _
                  "Initial Catalog=hdw2;" & _
                  "Integrated Security=SSPI;"


    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT SAF.Tracking_Number, ORH.CUSTOMER_PO_NUMBER,MAX(DOC.DOCUMENT_NUMBER),DOC.DOCUMENT_DATE_KEY,SAF.ITEM_NUMBER,  MAX(ITM.HOMECRAFT_ITEM_NUMBER), " _
        & "MAX(ITM.ITEM_DESCR),MAX(ADR.ADDR_NAME),MAX(ADR.ADDR1),MAX(ADR.ZIP_5_KEY),SUM(SAF.QUANTITY_SOLD),SUM(SAF.EXTENDED_AMOUNT) " _
        & ",SUM(SAF.EXTENDED_AMOUNT) / SUM(SAF.QUANTITY_SOLD) FROM SALES_FACT SAF  JOIN LU_CUSTOMER_TBL CUS ON SAF.CUSTOMER_KEY = CUS.CUSTOMER_KEY " _
        & "JOIN ORDER_DM ORH ON SAF.ORDER_KEY = ORH.ORDER_KEY JOIN PRODUCT_DM PRD ON SAF.PRODUCT_KEY = PRD.PRODUCT_KEY JOIN DOCUMENT_DM DOC " _
        & "ON SAF.DOCUMENT_KEY = DOC.DOCUMENT_KEY JOIN LU_ITEM ITM ON SAF.ITEM_NUMBER = ITM.ITEM_NUMBER JOIN LU_ADDRESS ADR ON DOC.SHIP_TO_ADDRESS_KEY = ADR.ADDRESS_KEY " _
        & "WHERE ITM.ITEM_CATEGORY_KEY NOT IN ('31010') AND SAF.TRACKING_CODE NOT IN ('F') AND PRD.PRODUCT_LINE_KEY NOT IN ('UN') AND PRD.PRODUCT_SUB_LINE_KEY NOT IN ('60P') " _
        & "AND SAF.TRACKING_NUMBER = " & contractNumber & " AND SAF.EXTENDED_AMOUNT > 0 AND DOC.DOCUMENT_DATE_KEY BETWEEN CONVERT(Date, " & startdate & " , 120) AND CONVERT(Date," & enddate & " , 120) " _
        & "GROUP BY ORH.CUSTOMER_PO_NUMBER, DOC.DOCUMENT_DATE_KEY,SAF.ITEM_NUMBER,SAF.Tracking_Number ORDER BY SAF.Tracking_Number,DOC.DOCUMENT_DATE_KEY")

    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets("Detail").Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing

End Sub

I assume that the cells on your sheet are formatted as "date" too. otherwise, try to convert the cells' values to date with cdate. My other suggestion on the first view is to use debug.print to get the sql statement and check if it is passed correctly to the database. — psychicebola
– psychicebola, Commented Aug 7, 2015 at 8:00
This is a hack instead of a real answer, but we used to always format dates as yyyymmdd to avoid such matters. SQL Server will interpret those correctly and without ambiguity. For example, like this: (...) BETWEEN '" & Year(startdate) & Format$(Month(startdate), "00") & Format$(Day(startdate), "00") & "' AND (...) (an utility function will simplify things). Note the apostrophes (we're passing dates as strings to SQL). — ssarabando
– ssarabando, Commented Aug 7, 2015 at 8:15
Hi ssarabando, worked perfectly! Had me scratching my head on this and this has helped me out immensely - thank you very much! Also, thanks to psychicebola and Mike for your answers too! — jamesc100
– jamesc100, Commented Aug 10, 2015 at 7:50

Mike Gledhill · Accepted Answer · 2015-08-07 08:58:07Z

2

The code I am using is below and the dates in the "Control" sheets are standard UK date - DD/MM/YYYY

Despite being a fellow Brit, I wouldn't recommend using your date format...

The safest way to pass dates to SQL Server is to use "yyyy-MM-dd". This guarantees it'll work, regardless of where you are in the world.

select * from [SomeTable]
where Update_Time >= '2015-08-06'

answered Aug 7, 2015 at 8:58

Mike Gledhill

29.4k8 gold badges160 silver badges162 bronze badges

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

Collectives™ on Stack Overflow

Passing Date variables in vba to sql statement

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related