Calender to select ranges from Access to Excel using VBA

caiyan

New Member
Joined
Apr 11, 2013
Messages
1
Goal: my goal is that the user can choose start date and end date use the calender implement in the userform, and then the VBA will automatically select the data range from access and updated into the defined worksheet in Excel. I managed to connect to access, just don't know how to implement the selection with the two date(the start date and end date). This is the sample code I implement:


Code:
Const strDb As String = "C:\Documents and Settings\YuC\My Documents\Database1.accdb"
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim strdate As String
Dim endate As String


Sheets("RawData").Select


  Const strQry As String = "SELECT * FROM [Calculation] WHERE Date BETWEEN strdate AND endate"


' Create the database connection
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDb & ";"


  Set rs = New ADODB.Recordset


  With rs
    Set .ActiveConnection = cn
        .Open strQry
  End With


The place I stucked is:
Code:
Const strQry As String = "SELECT * FROM [Calculation] WHERE Date BETWEEN strdate AND endate"
This will always give me "Run-time error: no value given for one or more required parameters".


Actually when I use for example:
Code:
Const strQry As String = "SELECT * FROM [Calculation] WHERE ID BETWEEN 50 AND 100"
This code will work and import the data from ID=50 to ID=100.


Thus the problem is that I should not use "strdate" and "endate" here:
Code:
Const strQry As String = "SELECT * FROM [Calculation] WHERE Date BETWEEN strdate AND endate"


Thus my question is how do I state the start date and end date into that statement as the two dates are defined by users and thus not constant.


Similar like that
If I just define ID number with i like this:
Code:
i=50
Const strQry As String = "SELECT * FROM [Calculation] WHERE ID BETWEEN i AND 100"
It will also not work.


Hope anyone can help solve my question and a lot of thanks.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,777
Messages
5,833,643
Members
430,221
Latest member
jmmccormick

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
Top