Parameter query with date picker and SQL Server backend.

[sparkbyte]

I am trying to run a query from a form with 2 date picker fields for start and end dates and having some problems.

I am getting a datatype conversion error.

Am I missing something? The parameters in the query are defined as Date/Time.

Here is the VBA code.

Private Sub btnOpneNatSecQuery_Click()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

'Debug.Print CDate(Me.DTPStart) & " And " & CDate(Me.dtpEnd)

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("Date Range Pending for National Security with Elapsed Time")
    qdf.Parameters("StartDate") = Me.DTPStart.Value
    qdf.Parameters("EndDate") = Me.dtpEnd.Value
    
Set rst = qdf.OpenRecordset("Date Range Pending for National Security with Elapsed Time", dbSeeChanges)

        
StatusBar ("National Security Cases")

'DoCmd.Close acForm, "National Security Query", acSaveNo

qdf.Close
Set qdt = Nothing
Set dbs = Nothing

End Sub

Thanks

John Fuhrman

 

[sparkbyte]

OK, this runs without any errors, but I need it to open the result set in a datasheet view.
What am I missing?

Private Sub btnOpneNatSecQuery_Click()
Dim strSQL As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set dbs = CurrentDb

strSQL = "SELECT [ID], [Login ID], [SEIPrint], [Start Time & Date], " & _
        "[Program Name], [Contact Name], [RPC Code], [NatSecButton], [FRCFileButton], " & _
        "[Digitized File], [Afile Number], [Memo], " & _
        "[Login1], [Reason for call], [Status of Activity] " & _
        "FROM Activity " & _
        "WHERE [Start Time & Date] BETWEEN #" & Me.DTPStart & "# And #" & Me.dtpEnd & "# AND [NatSecButton] <> 0 " & _
        "ORDER BY [ID];"

Debug.Print strSQL

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
'Set rst = dbs.OpenRecordset(strSQL, dbSQLPassThrough, dbOptimistic)

StatusBar ("National Security Cases")

End Sub

Thanks

John Fuhrman

 

[dhookom]

Why are you creating a recordset if you want to open a datasheet view?

I would go way back in the thread and modify as:

Dim strSQL as String
Dim strQueryName As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
strQueryName = "Date Range Pending for National Security with Elapsed Time"
strSQL = "SELECT ID, [Login ID], SEIPrint, [Start Time & Date], ElapsedTimeString([Activity]![Start Time & Date],Now()) AS ElapsedTime, " & _
    "[Program Name], [Contact Name], [RPC Code], NatSecButton, FRCFileButton, [Digitized File], [Afile Number], Memo, " & _
    "Login1, [Reason for call], [Status of Activity] " & _
    "FROM Activity " & _
    "WHERE [Start Time & Date] BETWEEN #" &  Me.DTPStart & "# And #" & Me.dtpEnd & "# AND NatSecButton=True " & _
    "ORDER BY ID; "
Set qdf = dbs.QueryDefs(strQueryName)
qdf.SQL = strSQL
DoCmd.OpenQuery strQueryName

Duane
Hook'D on Access
MS Access MVP