I have an excel sheet that is tapping an access database for data. I have fields (dates specifically) setup in excel that I want set as parameters when it hits those database queries.
In access my parameter type is Date/Time
Here's the code I'm trying
Using Excel/Access 2007, referencing Microsoft 12.0 Access Database something something.
I keep getting data mistmatch errors right before the record set is open. I've tried several things, like adding "#" before and after and converting it to a string, likewise with the parameter in access.
Anyone have any suggestions? It's just that date that is hanging it up, when I run only the "compare group" parameter it works perfect.
In access my parameter type is Date/Time
Here's the code I'm trying
Code:
Dim db As Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim compare_group As Date
Dim CY_Start As String
Dim CY_End As String
Dim Trend_Start As String
Dim Trend_End As String
Set db = OpenDatabase(Range("database_source_file").Value)
compare_group = Range("compare_group").Value
CY_Start = Range("CY_Start").Value
CY_End = Range("CY_End").Value
Set qdf = db.QueryDefs("All Indices - PSI")
qdf.Parameters("Compare Group").Value = compare_group
qdf.Parameters("CY_Start").Value = CDate(CY_Start)
qdf.Parameters("CY_End").Value = CDate(CY_End)
Set rst = qdf.OpenRecordset
Set qtbData = ActiveWorkbook.Sheets("test sheet").QueryTables.Add(rst, wksNew.Range("A2"))
'Refresh query table to display data.
qtbData.Refresh
rst.Close
qdf.Close
I keep getting data mistmatch errors right before the record set is open. I've tried several things, like adding "#" before and after and converting it to a string, likewise with the parameter in access.
Anyone have any suggestions? It's just that date that is hanging it up, when I run only the "compare group" parameter it works perfect.