Excel - using VBA to pass date parameter to Access

mmohon

New Member
Joined
Nov 19, 2009
Messages
38
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
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
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Maybe try

Code:
qdf.Parameters("CY_Start").Value = DateValue(CY_Start)
qdf.Parameters("CY_End").Value = DateValue(CY_End)
 
Upvote 0
maybe treat them the same as compare_group:
- declare them as Date instead of String
- remove the CDate() or DateValue() functions
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top