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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,741
Members
414,171
Latest member
12Rev79

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