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

 

[dhookom]

What were you seeing in the debug.print window?

Can you share the SQL of [Date Range Pending for National Security with Elapsed Time]?

Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

Here is the SQL code for the query.

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT Activity.ID, Activity.[Login ID], Activity.SEIPrint, Activity.[Start Time & Date], ElapsedTimeString([Activity]![Start Time & Date],Now()) AS ElapsedTime, Activity.[Program Name], Activity.[Contact Name], Activity.[RPC Code], Activity.NatSecButton, Activity.FRCFileButton, Activity.[Digitized File], Activity.[Afile Number], Activity.Memo, Activity.Login1, Activity.[Reason for call], Activity.[Status of Activity]
FROM Activity
WHERE (((Activity.[Start Time & Date])>=[StartDate] And (Activity.[Start Time & Date])<=[EndDate]) AND ((Activity.NatSecButton)=True))
ORDER BY Activity.ID;

and here is the output of the debug.print.

9/3/2015 5:00:00 AM And 11/17/2015 11:59:59 PM

Thanks

John Fuhrman

 

[dhookom]

I would simply update the SQL of the saved query to avoid the parameters entirely.

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], 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("Date Range Pending for National Security with Elapsed Time").SQL = strSQL
Set rst = qdf.OpenRecordset("Date Range Pending for National Security with Elapsed Time", dbSeeChanges)
[COLOR=#4E9A06]' --- etc ---[/color]

Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

For some reason I get a Type Mismatch when compiling on the second equals of this line.

Set qdf = dbs.QueryDefs("Date Range Pending for National Security with Elapsed Time").SQL [COLOR=#EF2929]=[/color] strSQL

Thanks

John Fuhrman

 

[dhookom]

My bad. I never use querydef parameters.

Dim strSQL as String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
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 rst = dbs.OpenRecordset(strSQL, dbSeeChanges)
[COLOR=#4E9A06]' --- etc ---[/color]

Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

No I get an invalid arguement.

Set rst = dbs.OpenRecordset(strSQL, dbSeeChanges)

Thanks

John Fuhrman

 

[sparkbyte]

Took the elapsed time portion out of the SQL just in case. But still has the same error.

SELECT 
[indent]ID, [Login ID], SEIPrint, [Start Time & Date], [Program Name], [Contact Name], [/indent]
[indent][RPC Code], NatSecButton, FRCFileButton, [Digitized File], [Afile Number], [/indent]
[indent]Memo, Login1, [Reason for call], [Status of Activity] [/indent]
FROM Activity 
WHERE [Start Time & Date] BETWEEN #9/3/2015 5:00:00 AM# And #11/18/2015 11:59:59 PM# AND NatSecButton=True 
ORDER BY ID;

Thanks

John Fuhrman

 

[dhookom]

What is the data type of [Start Time & Date] in the database?

Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

Just a regular datetime field. (not datetime2)

Thanks

John Fuhrman

 

[dhookom]

Is the query a pass-through or regular query?

What happens if you paste the SQL into a blank query and run it?


Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

A straight copy and paste from the debug.print runs the query just fine.

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 #9/3/2015 5:00:00 AM# And #11/19/2015 11:59:59 PM# AND NatSecButton=True ORDER BY ID;


Thanks

John Fuhrman

 

[dhookom]

Maybe share you current full code.

Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

SQL code

SELECT Activity.ID, Activity.[Login ID], Activity.SEIPrint, Activity.[Start Time & Date], Activity.[Closed Time & Date], Activity.[Program Name], Activity.[Contact Name], Activity.[RPC Code], Activity.NatSecButton, Activity.FRCFileButton, Activity.[Digitized File], Activity.[Afile Number], Activity.Memo, Activity.Login1, Activity.[Reason for call], Activity.[Status of Activity]
FROM Activity
WHERE (((Activity.[Start Time & Date]) Between #9/3/2015 5:0:0# And #11/19/2015 23:59:59#) AND ((Activity.[NatSecButton])=True))
ORDER BY Activity.ID;

VBA for the form.

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=True " & _
    "ORDER BY ID; "

Debug.Print strSQL

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

StatusBar ("National Security Cases")

End Sub

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 #9/3/2015 5:00:00 AM# And #11/19/2015 11:59:59 PM# AND NatSecButton=True ORDER BY ID;

I have changed the resultset to

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

From

Set rst = dbs.OpenRecordset(strSQL, dbSeeChanges)

But this doesn't change the errors. (Invalid Argument) Run-time Error 3001



Thanks

John Fuhrman

 

[dhookom]

Memo is a reserved word and may be causing an issue

http://www.allenbrowne.com/AppIssueBadWord.html#M.

Try wrap it in []s.

Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

Nope, took memo out and still have the same issue.

Thanks

John Fuhrman

 

[sparkbyte]

Took the where clause and order by out as well and still get the invalid argument.

I am at a loss.

Thanks

John Fuhrman

 

[dhookom]

Maybe the issue is with NatSecButton=True. Is NatSecButton a yes/no field?

Try: NatSecButton= -1

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Try with this minimal code:

Set rst = dbs.OpenRecordset(strSQL)

Duane
Hook'D on Access
MS Access MVP

 

[sparkbyte]

Well I might agree with that but, I commented out the entire WHERE clause and ORDER BY, and get the same invalid argument.

Thanks

John Fuhrman