Call a stored procedure in VBA while passing parameters with variables

[Glenley]

I'm trying to call a stored procedure in SQL Server with VBA. The stored proc has three parameters, @CC int, @SDate datetime2, @EDate datetime2. I'm able to get the SQL string correct for the integer but once I try to pass the dates, it throws errors. I'll paste the code below

    Dim db As Database, qd As QueryDef, strSQL As String
    Dim ConCompany As Long, SDate As Date, EDate As Date


    SDate = InputBox("Start date")
    EDate = InputBox("End date")
    ConCompany = Forms!MainMenuF!cmbCompany

    Set db = CurrentDb
    Set qd = db.QueryDefs("spApplicationReportPTQ")


    qd.Connect = ConString
    qd.SQL = "EXECUTE dbo.spApplicationReport " & ConCompany & ", " & SDate & "," & EDate
    DoCmd.OpenQuery ("spApplicationReportPTQ")


    qd.Close
    db.Close
    Set qd = Nothing
    Set db = Nothing

[Welshgasman]

Concatenate the dates with Format for USA dates.?

[Glenley]

I figured it out. Needed to surround each param with single quotes and then double

qd.SQL = "EXECUTE dbo.spApplicationReport '" & ConCompany & "', '" & SDate & "','" & EDate & "'"

[Welshgasman]

Really?
Surely that makes SDate and EDate just literals?

[Glenley]

SQL Server reads them correctly. It returns the desired records. I've tried tweaking the syntax a million different ways and this was the only way that worked. SDate and EDate are dimmed as Date and not String so maybe that's why

[June7]

SDate and EDate are not literals. They are concatenated variables in the code and their values are in the compiled and executed expression. The apostrophes are also concatenated into the compiled expression as data delimiters. A number type field should not need these delimiters. In an Access query the date values would be delimited with # characters.

[Welshgasman]

SDate and EDate are not literals. They are concatenated variables in the code and their values are in the compiled and executed expression. The apostrophes are also concatenated into the compiled expression as data delimiters. A number type field should not need these delimiters. In an Access query the date values would be delimited with # characters.

Yes, I see my mistake there, down to the fact I have never used a ' with dates, then again I have not used Acess with SQL Server either.

[ArviLaanemets]

To avoid all this hassle with various possible date formats and regional settings, I'd have both date parameters as integers. The calling app converts dates to integers and uses those, and the function will convert them to dates before using them. (In case you want to have a possibility to use dates as parameters too, format date parameters as Variant, and add additional parameter to determine date parameter type).

[Minty]

SQL server would want a date to be passed in as '2023-10-09' yyyy-mm-dd .

Apart from the delimiter Access will accept the same layout and it is unambiguous so I use it all the time.