Calender to select ranges from Access to Excel using VBA

[caiyan]

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:

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:

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:

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:

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:

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.