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.

[ssanfu]

- "Const" means constant. It doesn't (can't ) change. So if you define a string, "strQry" , as a constant, why would you think you could change a value in it?
- When using a variable in a string, you have to concatenate the variable to the string. If you use the variable name in the string, you are using the name of the variable, not the value.

Try adding the line in blue to your current code:

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

What is displayed in the message box? Not the dates!!

This works because you have numbers, not variables.

Const strQry As String = "SELECT * FROM [Calculation] WHERE ID BETWEEN 50 AND 100"
Msgbox strQry

This dosen't work because you need to concatenate the variable "i" to the string:

i=50
Const strQry As String = "SELECT * FROM [Calculation] WHERE ID BETWEEN i AND 100"
Msgbox strQry

This works because "i" is concatenated to the string:

i=50
Const strQry As String = "SELECT * FROM [Calculation] WHERE ID BETWEEN " & i & " AND 100"
Msgbox strQry

- You don't have values assigned to strdate and endate.

NOTE: "Date" is a reserved word in Access and shouldn't be used for object names. Plus it is not descriptive. "Date" of what? DOB, effective date, date entered, ....



I don't use ADO, so I can't help you with that part of the code.
But try this:

Option Explicit

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim strdate As String
Dim endate As String
Dim strQry As String
Dim strDb As String


Sheets("RawData").Select

strdate = <user Form control>  '<<= change to your control name
endate = <user Form control>   '<<= change to your control name

strDb  = "C:\Documents and Settings\YuC\My Documents\Database1.accdb"
strQry = "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

[caiyan]

Hi Steve, thanks for your reply with such detail explaining. Your code works perfect!!! I think I need more working to improve my understanding in this area.