Excel - using VBA to pass date parameter to Access

[mmohon]

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

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

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.

 

[Hermanito]

Maybe try

qdf.Parameters("CY_Start").Value = DateValue(CY_Start)
qdf.Parameters("CY_End").Value = DateValue(CY_End)

 

[mmohon]

Gave that a try, same thing happened

 

[Hermanito]

maybe treat them the same as compare_group:
- declare them as Date instead of String
- remove the CDate() or DateValue() functions

 

[mmohon]

I actually found my issue, and sadly it wasn't with the dates. It was with the other parameter, I was trying to pass a string when it wanted an integer. Got it fixed!

Thanks for your help and suggestions.

 

[Hermanito]

Glad you found the issue, thanks for the feedback.