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:
The place I stucked is:
This will always give me "Run-time error: no value given for one or more required parameters".
Actually when I use for example:
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:
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:
It will also not work.
Hope anyone can help solve my question and a lot of thanks.
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"
Actually when I use for example:
Code:
Const strQry As String = "SELECT * FROM [Calculation] WHERE ID BETWEEN 50 AND 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"
Hope anyone can help solve my question and a lot of thanks.