VBA Code Help IN CMD.Parameter

[lokeshsu]

Hi all,

I need some help in the below code. i have saved the sql query in access and trying to execute the sql by passing parameters from excel. below is the code which i have coded in vba and i have also added the sql query. i am getting a error like no records found when trying to run the query.

VBA CODE

    cmd.ActiveConnection = con
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "Passed SLa Query"
    cmd.Parameters.Append cmd.CreateParameter("Process", adVarChar, adParamInput, 6, mProcess)
    cmd.Parameters.Append cmd.CreateParameter("Open", adDate, adParamInput, 10, opn)
    cmd.Parameters.Append cmd.CreateParameter("close", adDBTimeStamp, adParamInput, 20, clos)
    cmd.Parameters.Append cmd.CreateParameter("Prior", adVarChar, adParamInput, 10, Range("A" & a).Value)
     Set rs = cmd.Execute


Do Until rs.EOF Or rs.BOF
    If rs!Expr2 = 0 Then
    passed = passed + 1
    Else
    End If
rs.MoveNext
Loop

The open & closed date is in the format mm/dd/yy hh:mm:ss

SQL QUERY

SELECT DISTINCT [Union Query].SRNo_Out, Min(Inbound.ActivityCreatedDate) AS MinOfActivityCreatedDate1, Min(Outbound.ActivityCreatedDate) AS MinOfActivityCreatedDate, DateDiff("d",[MinOfActivityCreatedDate1],[MinOfActivityCreatedDate])-(DateDiff("ww",[MinOfActivityCreatedDate1],[MinOfActivityCreatedDate],1)+DateDiff("ww",[MinOfActivityCreatedDate1],[MinOfActivityCreatedDate],7)) AS Expr2
FROM ([Union Query] INNER JOIN Inbound ON [Union Query].SRNo_Out = Inbound.SRNo_In) INNER JOIN Outbound ON [Union Query].SRNo_Out = Outbound.SRNo_Out
WHERE ((([Union Query].Segment)=[process]) AND ((Inbound.OpenedDate) Between [OPen] And [Close]) AND ((Outbound.OpenedDate) Between [OPen] And [Close]) AND (([Union Query].Priority)=[priorit]))
GROUP BY [Union Query].SRNo_Out, DateDiff("d",[MinOfActivityCreatedDate1],[MinOfActivityCreatedDate])-(DateDiff("ww",[MinOfActivityCreatedDate1],[MinOfActivityCreatedDate],1)+DateDiff("ww",[MinOfActivityCreatedDate1],[MinOfActivityCreatedDate],7));